Click here to Skip to main content
14,695,085 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
MyRange.FormatConditions.Delete

'Add LATE rule
MyRange.FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
        String:="LATE"
MyRange.FormatConditions(1).Interior.Color = vbRed
'Add RISK rule
MyRange.FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
        String:="RISK"
MyRange.FormatConditions(2).Interior.Color = RGB(255, 153, 0)
'Add WATCH rule
MyRange.FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
        String:="WATCH"
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
or
MyRange.FormatConditions(1).EntireRow.Interior.Color = vbRed

would work, however I get a "483" error if I try that.
Posted
Updated 22-Sep-20 21:50pm
Comments
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...
   
Comments
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