Conditional Formatting in MS Excel

Eileen

New Member
Can anyone assist me . I am trying to add conditional formatting into a spreadsheet from progress.

I have recorded the macro in excel to see if I can work out the progress lines.
I need to change the background colour depending on 2 formula.
So to set cell B3 to colour 34 if ah3 > 0 or to colour 39 if d3 > e3.
Range("B3").Select -
Color numbers are not the ones I want but do for a sample.
Cells.FormatConditions.Delete
Range("B3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ah3 > 0"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""d3 > e3"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

I have got the code to :-
chworksheet:range("A" + STRING(lxx) + ":B" + STRING(lxx)):formatconditions:ADDTYPE:xlexpression:formula1="=D" + STRING(lxx) + "> E" + STRING(lxx).

chworksheet:range("A" + STRING(lxx) + ":B" + STRING(lxx)):formatconditions:Interior:colorindex = 39.
chworksheet:range("A" + STRING(lxx) + ":B" + STRING(lxx)):formatconditions:ADDTYPE:xlexpression:formula1="AH" + STRING(lxx) + "> 0" .

chworksheet:range("A" + STRING(lxx) + ":B" + STRING(lxx)):formatconditions:Interior:colorindex = 34.


I know I am completely wrong on this . Any help would be appreciated.
 
Unfortunately I don't think so That allow you to set an icon against a cell what I need is to colour fill the celldepending on 2 formula.

In excel under cond. formatting add in formatting via rules manager
1719411008754.png
I just cant see how to convert the Macro lines above to progress lines as the syntax will not work.
 
Yes, the macro code helps to a degree but the actual solutions may have to be found online. To convert this code:

Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ah3 > 0"""

Try this site:


Where you see:

expression.Add (Type, Operator, Formula1, Formula2)

So you could be looking to having something similar to this - the 2 is for xlExpression (XlFormatConditionType enumeration (Excel)):

Code:
chworksheet:Range("A" + STRING(lxx) + ":B" + STRING(lxx)):FormatConditions:Add(2, , "=" + QUOTER("ah3 > 0"), "=$B$3").

This may not be completely correct but it hopefully points you in the right direction.
 
Last edited:
Here is a sample I wrote a while back, it may help:
Code:
// add conditional formatting for row shading; every other group of 3 rows is light gray
v-worksheet:Range( "A4" ):Select.
v-worksheet:Range( v-excel:Selection, v-excel:ActiveCell:SpecialCells( xlLastCell ) ):Select.
v-excel:Selection:FormatConditions:Add( xlExpression, , "=mod(trunc((row(A4)-1)/3),2)=0" ).
v-excel:Selection:FormatConditions( 1 ):SetFirstPriority.
v-excel:Selection:FormatConditions( 1 ):Interior:TintAndShade = -.0499893185216834. // white, background1, darker 5%

In this case, v-excel and v-worksheet are com-handle variables; v-excel is the Excel.Application COM object and v-worksheet is set to the active worksheet. Note also that in Excel conditional formats, cell references are relative (e.g. "A4" above) unless otherwise made absolute (e.g. "$A$4"). With a relative reference, Excel copies the format to other cells in the selection as apporpriate; I didn't have to do that programmatically in the ABL.

In addition to creating Excel macros and looking at the resulting VBA code, it can also be helpful to use the OE COM Object Viewer (%DLC%\bin\proobjvw.exe), so you understand the parameters to use with methods and properties. Note though that it doesn't always agree with the runtime in terms of which arguments are optional.

1719594308857.png

I hope this helps. Let me know if you have more questions.
 
Thanks for the help so far, but I am still struggling with this. Can anyone spot a stupid mistake as I am getting and error on 1 line when I run this.

/* lcnt = line counter. mcnt is column count colname is M N O P etc.*/


vdet1 = "= " + colname[mcnt - 2] + STRING(lcnt) + " > 0 ".
vdet2 = "= e" + STRING(lcnt) + " > d" + STRING(lcnt) .
MESSAGE "1".
chworksheet:Range("A" + STRING(lcnt) + ":B" + STRING(lcnt)):FormatConditions:ADD ( 2, ,vdet1, vdet2).
MESSAGE "1a".
chworksheet:Range("A" + STRING(lcnt) + ":B" + STRING(lcnt)):FormatConditions(1):interior:colorindex = 34.
MESSAGE "1b".
chworksheet:Range("A" + STRING(lcnt) + ":B" + STRING(lcnt)):FormatConditions(2):interior:colorindex = 39.
MESSAGE "1c".

This is part of a for each.
I get :-
1, 1a, 1b
1719838928614.png
It then it loops the for each ut does not move the row on even though lcnt is lcnt + 1.
Then next time I get 1 , 1a, 1b, 1c and then repeats but moves to the next line

In the spreadsheet it is showing :-

1719839932468.png

I have gone round and round with this and cannot see what I am doing wrong.

Has anyone got any ideas. ?
 
What I should have said on the same fields I have requirement for 2 conditional formats One colour if ah > 0 and the other colour if e is greater than d. They will never be both at the same time.
 
I am not sure, but I wonder if the problem is you have only added one FormatConditions, which explains why FormatConditions(1) is okay but not FormatConditions(2)?
 
I thought I had in your sample :-
1719842851536.png
and mine
1719842900454.png

vdet1 is the 1st condition vdet 2 is the 2nd.


I have done a work around for the time being in that condition 1 is now on A and condition 2 is on B but I would like to have them both on A-B eventually. In the meantime it runs !
 

Attachments

  • 1719842719173.png
    1719842719173.png
    6.7 KB · Views: 2
I think you are missing is a second addition and also do not require the second formula to be part of the first addition. Try changing the first addition and then have a second addition similar to this:

Code:
chworksheet:Range("A" + STRING(lcnt) + ":B" + STRING(lcnt)):FormatConditions:Add ( 2, , vdet1).
chworksheet:Range("A" + STRING(lcnt) + ":B" + STRING(lcnt)):FormatConditions:Add ( 2, , vdet2).
 
Back
Top