Click here to Skip to main content
14,667,947 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Guys,

New to VBA, trying to automate a open orders report. My data set is A2 through X10999. If an order is late, then starting in J2 there will be text "LATE". If this is the case then I need the whole row to be formatted red. After a lot of Googling and debugging I have the following code that will color only the "late" cell red. I can not figure out how to get the whole row to be red.

Any help would be appreciated!

Sub ConditionalFormatting()

Dim MyRange As Range
'Create range object
Set MyRange = Range("A2:x10999")
'Delete previous conditional formats

'Add LATE rule
MyRange.FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
MyRange.FormatConditions(1).Interior.Color = vbRed
'Add RISK rule
MyRange.FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
MyRange.FormatConditions(2).Interior.Color = RGB(255, 153, 0)
'Add WATCH rule
MyRange.FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
MyRange.FormatConditions(3).Interior.Color = vbYellow

End Sub

What I have tried:

I have tried different variations and even thought that
MyRange.FormatConditions(1).Row.Interior.Color = vbRed
MyRange.FormatConditions(1).EntireRow.Interior.Color = vbRed

would work, however I get a "483" error if I try that.
Updated 22-Sep-20 20:50pm
Maciej Los 23-Sep-20 2:16am
Please, define what you mean by "If an order is late, ...". Probably you need to add a comparison of an order date to actual date.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Try this expression:
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$J2=""LATE"""

This will set the conditional formatting in all the cells of a row to look at the value in Jn...
Maciej Los 23-Sep-20 3:48am
Looks perfect to me. 5ed!
Member 14946076 23-Sep-20 10:45am
Thanks Member 12901943!!! That worked!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100