Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capture conditional format as cell format | Excel Discussion (Misc queries) | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
Multiple conditional on conditional format formula | Excel Programming | |||
Multiple conditional on conditional format formula | Excel Programming | |||
Keep format after paste from other worksheets - conditional format or EnableControl solution doesn't work | Excel Programming |