Reputation: 53
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
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