Click here to Skip to main content
15,116,516 members
Please Sign up or sign in to vote.
4.00/5 (1 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 21: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

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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900