jazz2409
04-27-2020, 06:46 PM
Hello again, I have this formula for my conditional formatting:
=COUNTIF('Sheet2'!$W:$W,$B8)=1
and my VBA code:
Dim rng As Range
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Range("B8:O" & Lastrow)
rng.FormatConditions.Delete
With rng
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF('Sheet2'!$W:$W,$B8)=1"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
With .Font
.Bold = True
End With
End With
End With
It highlights the range based on whether the value on column B can be found on column W of Sheet 2.
How do I change the formula to also include a condition that if the values in column C to O of the same highlighted rows are less than 100, the font color will be changed to red?
here's my workbook:
Thank you :)
=COUNTIF('Sheet2'!$W:$W,$B8)=1
and my VBA code:
Dim rng As Range
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Range("B8:O" & Lastrow)
rng.FormatConditions.Delete
With rng
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF('Sheet2'!$W:$W,$B8)=1"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
With .Font
.Bold = True
End With
End With
End With
It highlights the range based on whether the value on column B can be found on column W of Sheet 2.
How do I change the formula to also include a condition that if the values in column C to O of the same highlighted rows are less than 100, the font color will be changed to red?
here's my workbook:
Thank you :)