Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting in VBA
hi all,
your help is mostly appreciated on following... Note: the column D used below is only for example, when executed, the code loops through each column of selection ...i use Conditional Formatting manually by adding a formula (=SUM(IF($D$5:$D$9="",0,1))<0) into $D$4 with appropriate setting and all is well - if either cell D5, D6, D7, D8 and/ or D9 has a value, D4 will be formatted as specified. however if i use this same method in VBA, D4 is only formatted as specified if D5 has a value, but D6, D7, D8 and/ or D9 are ignored. - this is the code i use Worksheets(sName) _ .Range("A1") _ .Offset(3, iColumn2) _ .FormatConditions.Delete Worksheets(sName) _ .Range("A1") _ .Offset(3, iColumn2) _ .FormatConditions .Add Type:=xlExpression, Formula1:= "=SUM(IF(" & Worksheets(sName). _ Range("A1").Offset(4,iColumn2).Address & ":" & Worksheets(sName). _ Range("A1").Offset(8, iColumn2).Address & "="""",0,1))<0" Worksheets(sName) _ .Range("A1") _ .Offset(3, iColumn2) _ .FormatConditions(1) _ .Interior _ .Pattern = xlNone With Worksheets(sName) _ .Range("A1") _ .Offset(3, iColumn2) _ .FormatConditions(1) _ .Font .Bold = False .Italic = False .ColorIndex = xlAutomatic End With (the formula line is all one line) thank you in advance for your help... ...jurgenC! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |