CODEWITHSUNDEEP
vbaexcel
Sergio Oropeza
Sergio Oropeza

Reputation: 53

Conditional formatting VBA not highlighting cell in a specific cell in a worksheet

I had a quick question about the conditional formatting part of my code,

the rest of the code works perfect but when I get to the conditional formatting part, the row I wanted to highlight doesn't. I want the row with to be highlighted based on a value that is on cell H5 on the copysheet area.

cell H5 has the formala =MAX(IF(ISERROR("L8:L2500,"",L8:2500") which finds the max value in range L

is there any way to apply conditional formatting to search for that max value? 

Sub Search()

Dim vCrit As Variant
Dim filtersheet As Worksheet
Dim copysheet As Worksheet
Dim rngCrit As Range
Dim rngList As Range



Set filtersheet = Worksheets("Datalog")
Set copysheet = Worksheets("Line Inquiry")


filtersheet.Unprotect


Set rngCrit = filtersheet.Range("$A$7:$L$2500").CurrentRegion
Set rngList = copysheet.Range("B5")



vCrit = Sheets("Datalog").Range("O3")

copysheet.Range("A8:K2500").Clear


With filtersheet
rngCrit.AutoFilter _
Field:=3, _
Criteria1:=Application.Transpose(vCrit), _
Operator:=xlFilterValues



rngCrit.SpecialCells(xlCellTypeVisible).Copy
copysheet.Range("A7").PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False

Application.ScreenUpdating = False
filtersheet.AutoFilterMode = False


filtersheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

copysheet.Range("A8:L2500").FormatConditions.Delete

With copysheet.Range("$A8:$L8").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$H$5=$L8")
.Interior.Color = RGB(198, 239, 206)
.Font.Color = RGB(0, 97, 0)


    End With

End With

'returning to original sheet
copysheet.Range("B5").Select

End Sub

Upvotes: 1

Views: 112

Answers (1)

MatthewD
MatthewD

Reputation: 6801

You could do your formatting in your code. Something like this.

if somecondition then
    copysheet.Range("I" & lRow).Font.ColorIndex = 3
End if

Upvotes: 1

Related Questions