Conditional Formatting using formula as condition

[jazz2409]

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 :)

[paulked]

Hi Jazz. It's better to keep your code together rather than split things up and, in my opinion, not to mix conditional formatting with vba.

See your last thread (http://www.vbaexpress.com/forum/showthread.php?67238-Change-font-size-of-rows-in-a-range-with-bold-letters&p=401401&viewfull=1#post401401) for colours.

Also, as you got your sheets mixed up last time :wink: then perhaps post your 'real' workbook?

[jazz2409]

what do you mean I got my workbooks mixed up?

[paulked]

Update: it's working. I just forgot to change the names of the sheets :facepalm: :peace:

[jazz2409]

Oh my God I thought I uploaded the real workbook with all client data I checked all my posts :banghead: