Conditional Format
 

I have the following VBA to enter, copy and paste conditional formats. But
it gets stuck at the first ".Add (xlExpression,...)" line.

This is what I want it to do:
If I2 is less than or equal to 60 from today and greater than 30 days from
today and A2 is not blank then highlight A2:J2 color 34...
Or
If I2 is less than or equal to 30 from today and greater than today and A2
is not blank then highlight A2:J2 color 36...
Or
If I2 is less today and A2 is not blank then highlight A2:J2 color 38 and
change font to white...
then copy formula down to last row.

Here is the current code:
With Range("a2:j2").FormatConditions
.Delete
.Add(xlExpression, ,
"=and($i2<=(today()+60),$i2(today()+30),$a2<"")" ).Interior.ColorIndex = 34
.Add(xlExpression, ,
"=and($i2<=(today()+30),$i2(today()),$a2<"")").I nterior.ColorIndex = 36
.Add(xlExpression, ,
"=and($i4<today(),$a2<"")").Interior.ColorInd ex = 38
End With
With Range("a2:j2")
.Copy
Range("a2:j" & Range("j65536").End(xlUp).Row).PasteSpecial
xlPasteFormats
End With

Conditional Format
 

My apologizes for posting this twice...my browser closed down, I wasn't sure
that the post went through...

"Josh O." wrote:

I have the following VBA to enter, copy and paste conditional formats. But
it gets stuck at the first ".Add (xlExpression,...)" line.

This is what I want it to do:
If I2 is less than or equal to 60 from today and greater than 30 days from
today and A2 is not blank then highlight A2:J2 color 34...
Or
If I2 is less than or equal to 30 from today and greater than today and A2
is not blank then highlight A2:J2 color 36...
Or
If I2 is less today and A2 is not blank then highlight A2:J2 color 38 and
change font to white...
then copy formula down to last row.

Here is the current code:
With Range("a2:j2").FormatConditions
.Delete
.Add(xlExpression, ,
"=and($i2<=(today()+60),$i2(today()+30),$a2<"")" ).Interior.ColorIndex = 34
.Add(xlExpression, ,
"=and($i2<=(today()+30),$i2(today()),$a2<"")").I nterior.ColorIndex = 36
.Add(xlExpression, ,
"=and($i4<today(),$a2<"")").Interior.ColorInd ex = 38
End With
With Range("a2:j2")
.Copy
Range("a2:j" & Range("j65536").End(xlUp).Row).PasteSpecial
xlPasteFormats
End With

Conditional Format
 

Ok...I figured out the other thing. But now it is entering the row number in
the conditional format incorrectly (off by one row).

For example, in row 2 it enters:
=AND($I3<=(TODAY()+60),$I3(TODAY()+30),$I30)
Instead of:
=AND($I2<=(TODAY()+60),$I2(TODAY()+30),$I20)

Here is the code as of now:
With Range("a2:j2").FormatConditions
.Delete
.Add(xlExpression, ,
"=AND($I2<=(TODAY()+60),$I2(TODAY()+30),$I20)"). Interior.ColorIndex = 34
.Add(xlExpression, ,
"=AND($I2<=(TODAY()+30),$I2(TODAY()),$I20)").Int erior.ColorIndex = 36
.Add(xlExpression, , "=AND($I2<TODAY(),$I20)").Interior.ColorIndex = 38
End With
Range("a2:j2").Copy
Range("a2:j" & Range("a65000").End(xlUp).Row).PasteSpecial xlPasteFormats

"Josh O." wrote:

My apologizes for posting this twice...my browser closed down, I wasn't sure
that the post went through...

"Josh O." wrote:

I have the following VBA to enter, copy and paste conditional formats. But
it gets stuck at the first ".Add (xlExpression,...)" line.

This is what I want it to do:
If I2 is less than or equal to 60 from today and greater than 30 days from
today and A2 is not blank then highlight A2:J2 color 34...
Or
If I2 is less than or equal to 30 from today and greater than today and A2
is not blank then highlight A2:J2 color 36...
Or
If I2 is less today and A2 is not blank then highlight A2:J2 color 38 and
change font to white...
then copy formula down to last row.

Here is the current code:
With Range("a2:j2").FormatConditions
.Delete
.Add(xlExpression, ,
"=and($i2<=(today()+60),$i2(today()+30),$a2<"")" ).Interior.ColorIndex = 34
.Add(xlExpression, ,
"=and($i2<=(today()+30),$i2(today()),$a2<"")").I nterior.ColorIndex = 36
.Add(xlExpression, ,
"=and($i4<today(),$a2<"")").Interior.ColorInd ex = 38
End With
With Range("a2:j2")
.Copy
Range("a2:j" & Range("j65536").End(xlUp).Row).PasteSpecial
xlPasteFormats
End With