Multiple Conditional Formatting Rules in VBA

[NEW_2VBA]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Invoice Date
[/TD]
[TD]Amount
[/TD]
[TD]Check Date
[/TD]
[TD]Check #
[/TD]
[TD]$ Inv Paid
[/TD]
[TD]Check $
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6/1/2002
[/TD]
[TD]$5000
[/TD]
[TD]6/23/2002
[/TD]
[TD]1234
[/TD]
[TD]$0.00
[/TD]
[TD]$0.00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]5/1/2003
[/TD]
[TD]$4400
[/TD]
[TD]5/15/2003
[/TD]
[TD]4576
[/TD]
[TD]$4400
[/TD]
[TD]$8000
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]7/5/2004
[/TD]
[TD]$100
[/TD]
[TD]7/28/2004
[/TD]
[TD]9886
[/TD]
[TD]$500
[/TD]
[TD]$6000
[/TD]
[/TR]
</tbody>[/TABLE]

Happy New Year! I need some guidance for creating vba code to run multiple conditional formatting rules.

1. On Column F = D2+20>F2 Check date must be 20 days after invoice date, if not = condl highlight
2. On Column H = H2<>E2 Amount should = $ Inv Paid, if not = condl highlight
3. On Column I = I2<H2 Check $ must be greater than $ Inv Paid, if not = condl

For all rules I'd just like to highlight the cell if the value is true Interior.Color=RGB(248,203,173)




Here's what I have so far..

With Range("F2").FormatConditions.Add(Type:=xlExpression, Formula1:="=D2+20>F2")
.Interior.Color = RGB(248, 203, 173)
End With
Range("F2").NumberFormat = "m/d/yyyy"

With Range("H2").FormatConditions.Add(Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=E2")
.Interior.Color = RGB(248, 203, 173)
End With
With Range("I2").FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=H2")
.Interior.Color = RGB(248, 203, 173)
End With

 

[Fluff]

How about

Dim Lr As Long
Lr = Range("D" & Rows.Count).End(xlUp).Row
With Range("F2:F" & Lr).FormatConditions.Add(Type:=xlExpression, Formula1:="=D2+20>F2")
   .Interior.Color = RGB(248, 203, 173)
End With
Range("F2:F" & Lr).NumberFormat = "m/d/yyyy"

With Range("H2:H" & Lr).FormatConditions.Add(Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=E2")
   .Interior.Color = RGB(248, 203, 173)
End With
With Range("I2:I" & Lr).FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=H2")
   .Interior.Color = RGB(248, 203, 173)
End With

 

[NEW_2VBA]

Thanks for the feedback. I used the code above & checked "Manage Rules" and noticed all three conditional formatting rules were listed however only the first one was applied.
I read something about specifying "StopifTrue=False" or possibly setting priority but not too familiar with adding those instructions into code.

 

[Fluff]

This is what I get with that code

+Fluff 1.xlsm
	D	E	F	G	H	I
1	Invoice Date	Amount	Check Date	Check #	$ Inv Paid	Check $
2	01/06/2002	5000	23/06/2002	1234	0	0
3	01/05/2003	4400	15/05/2003	4576	4400	4000
4	05/07/2004	100	28/07/2004	9886	500	6000
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F4	Expression	=D2+20>F2	text	YES
H2:H4	Cell Value	<>E2	text	YES
I2:I4	Cell Value	<H2	text	YES