SteveM99
08-03-2018, 07:24 AM
I need help with coding vba conditional formatting. I am trying to compare a column of numbers to each other and color the cells which do not match. Either color both cells or the cells in the right column (column J).
I researched and attempted but got stuck and needed some help. Below is my code
Sub AA450_CONDITIONAL1()
Dim rg As Range
Dim cond1 As FormatCondition
'GET LAST ROW IN COLUMN J IN THE RECAP SHEET FOR THIS MACRO
Dim Lastrow7 As Long
Lastrow7 = Worksheets("RECAP").Columns("O").Find("*", SEARCHDIRECTION:=xlPrevious, SEARCHORDER:=xlByRows, LookIn:=xlValues).Row
'SET THE RANGE
Set rg = Range("J5:J" & Lastrow7)
'clear any existing conditional formatting
'rg.FormatConditions.Delete
Worksheets("RECAP").Columns("J").FormatConditions.Delete
Worksheets("RECAP").Activate
'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$H$5:$H$" & Lastrow7)
'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With
End Sub
I researched and attempted but got stuck and needed some help. Below is my code
Sub AA450_CONDITIONAL1()
Dim rg As Range
Dim cond1 As FormatCondition
'GET LAST ROW IN COLUMN J IN THE RECAP SHEET FOR THIS MACRO
Dim Lastrow7 As Long
Lastrow7 = Worksheets("RECAP").Columns("O").Find("*", SEARCHDIRECTION:=xlPrevious, SEARCHORDER:=xlByRows, LookIn:=xlValues).Row
'SET THE RANGE
Set rg = Range("J5:J" & Lastrow7)
'clear any existing conditional formatting
'rg.FormatConditions.Delete
Worksheets("RECAP").Columns("J").FormatConditions.Delete
Worksheets("RECAP").Activate
'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$H$5:$H$" & Lastrow7)
'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With
End Sub