highlight cell based on criteria

[jetset1]

hey everyone,

I have a variable number of rows in a worksheet.
column A can be either A, B, C, D, E- there can be many A, B, C, D or E's
Column B is just a prefix
Column C can be H or L
Column D is value or any amount

For item A i would like to highlight the highest value when Column C is H

For item B, C, D or E i would like to highlight the lowest value when Column C is L

an example has been attached.

Greatly apreicated

[XOR LX]

Hi,

Based on your attachment, with D2:D20 highlighted and D2 the active cell, create two formula-based Conditional Formattings viz:

Rule 1: =AND(C2="L",A2<>"A",E2=MIN(IF($C$2:$C$20="L",IF($A$2:$A$20=A2,$E$2:$E$20))))

Rule 2: =AND(C2="H",A2="A",E2=MAX(IF($C$2:$C$20="H",IF($A$2:$A$20="A",$E$2:$E$20))))

Regards

[ragulduy]

Maybe a helper column and conditional formatting is the way to go here?
Something like:
=IF(D2=MAX(IF($A2:$A20="A",1,0)*IF($C2:$C20="H",1,0)*$D2:$D20),TRUE,FALSE)
in E2 and copy down (it's an array formula so confim with ctrl+shift+enter)
then your conditional formatting would be =E2

To change to the lowest value just change the 1,0 to 1,1000000 and max to min

[jetset1]

Hi,

Based on your attachment, with D2:D20 highlighted and D2 the active cell, create two formula-based Conditional Formattings viz:

Rule 1: =AND(C2="L",A2<>"A",E2=MIN(IF($C$2:$C$20="L",IF($A$2:$A$20=A2,$E$2:$E$20))))

Rule 2: =AND(C2="H",A2="A",E2=MAX(IF($C$2:$C$20="H",IF($A$2:$A$20="A",$E$2:$E$20))))

Regards

Hey thanks for your response.

I would like this to be in VBA..

[jetset1]

Hi, if this can be done in VBA i would be over the moon.

[XOR LX]

Why do you want Conditional Formatting done in VBA??

[jetset1]

It is in a workbook that gets sent to me every week. I can adjust the code to run on the workbook.

[ragulduy]

Agree with XOR LX that you should use conditional formatting (or assign the conditional formatting using VBA) but you can just put XOR LX's formulas in VBA:

Please Login or Register  to view this content.

[jetset1]

i see, i will try to do that.

Cheers guys,

[jetset1]

I cant seem to convert the formula your provided into something i can adapt for a loop - could you help


If Evaluate("=AND(Cells(i, 3))=""L"",Cells(i, 1)<>""A"",Cells(i, 4)=MIN(IF(Range(Cells(2, 3), Cells(20, 3)=""L"",IF(Range(Cells(2, 1), Cells(20,1))=Cells(i,1),Range(Cells(2,4),cells(20,4)))))") = True Then Cells(i, 4).Interior.ColorIndex = 3

i would = 2 for the start of my loop

If i can get this working i should be able to get the loop working - any help appreciated

[jetset1]

hey guys,

I applied the following VBA. While the conditons are created it doesnt want to apply -why?

Sub Add_CF()

With Sheet1
Range("D2:D20").FormatConditions.Delete
With .Range("D2:D20,D2")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=AND(C2=""H"",A2=""A"",D2=MAX(IF($C$2:$C$20=""H"",IF($A$2:$A$20=""A"",$D$2:$D$20))))"
.FormatConditions(1).Borders.LineStyle = xlContinuous
.FormatConditions(1).Borders.Weight = xlThin
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 11
End With
With .Range("D2:D20,D2")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=AND(C2=""L"",A2<>""A"",D2=MIN(IF($C$2:$C$20=""L"",IF($A$2:$A$20=A2,$D$2:$D$20))))"
.FormatConditions(1).Borders.LineStyle = xlContinuous
.FormatConditions(1).Borders.Weight = xlThin
.FormatConditions(1).Interior.ColorIndex = 7
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 11
End With
End With

End Sub

[ragulduy]

It looks to me like maybe those are array formulas which don't work in conditional formatting? I could have made that up though, formulas isn't really my strong point.

[XOR LX]

All formulas in Conditional Formatting are automatically array-entered (so no need for curly brackets or CTRL+SHIFT+ENTER).

No idea how array formulas work with Conditional Formatting in VBA though.

Regards

[ragulduy]

That is really bizarre, far as I can tell, when you enter the array formula via VBA it doesn't work but if you open the conditional format window and then press enter it is applied. I can't find a way round this other than looping through with sendkeys to mimick reconfirming all the format conditions. Note, in your code you apply both sets of format conditions to format condition 1, you need to change the (1) to (2) for the second set:

Because I'm using sendkeys you can't trigger this from the vba developer window, you need to trigger it from your excel spreadsheet with either a button or a shortcut.

Please Login or Register  to view this content.

[XOR LX]

Apologies all. Just realised that my original formulas were referencing the wrong column. Should've been:

Rule 1: =AND(C2="L",A2<>"A",D2=MIN(IF($C$2:$C$20="L",IF($A$2:$A$20=A2,$D$2:$D$20))))

Rule 2: =AND(C2="H",A2="A",D2=MAX(IF($C$2:$C$20="H",IF($A$2:$A$20="A",$D$2:$D$20)))

@yudlugar

Very sorry if this messes up all the hard work you've done with the VBA conversion. I guess the general issue still remains of Conditional Formatting with array formulas in VBA, though. I'll have a look at finding some non-array alternatives for the CF whilst we wait for someone to come back on that.

Regards

[ragulduy]

jetset fixed the column reference in their code.

It works providing you go back through and reconfirm all the conditional formatting boxes. I guess you could probably do it without a loop actually:

Please Login or Register  to view this content.

[jetset1]

Yes exactly you can open up the conditional formatting window(s) and then apply it. Weird!

Il have a look into sticky keys

[jetset1]

Hi XOR LX,

Thats okay i picked up on that - it was the idea that counted

[XOR LX]

"Hi XOR LX,

Thats okay i picked up on that - it was the idea that counted"

I'm glad someone's awake then!

[Izandol]

Array formulas in conditional formatting with vba have been a problem for many years. You may avoid it by using INDEX in your formulas:

Please Login or Register  to view this content.

Usually it is better not to do such complex calculations in the formatting formula which is very volatile.

[jetset1]

Hi Izandol this hasnt applied the conditional formatting which is the problem i am having. It creates them just fine though.

If i were to sum the cells and put the output in say Cell K2 and K3 rather than colour them in, how would i do that? - maybe then i can avoid conditional formatting altogether which seems volatile


"=AND(C2=""H"",A2=""A"",D2=MAX(INDEX(($C$2:$C$20=""H"")*($A$2:$A$20=""A"")*$D$2:$D$20,)))"

"=AND(C2=""L"",A2<>""A"",D2=1/MAX(INDEX(($C$2:$C$20=""L"")*($A$2:$A$20=A2)*1/$D$2:$D$20,)))"

[Izandol]

I have tested this code in xl2013 and it is working here to apply the format (while the original code did not) - I may not test with xl2007 as I do not have it.

I do not understand which cells you want to sum or why sum them?

[Izandol]

I have now also tested this code with xl2010 and it is working there also.

[jetset1]

Hey,

I am using 2007 and it doesnt seem to work,

I would be looking to sum, each colour of what would be highlighted cells of column D if i had a version of excel >2007.

[Izandol]

It must be my English but I still do not understand. What is the meaning of "sum, each colour"?

[jetset1]

Hi Izandol

I think my english may be bad looking back on it. lol

Okay so at the moment the conditional formatting finds "A" in column A and "H" in column C and then looks for the highest value in column D. It then highlights this cell.

The value of this highlighted cell is of interest to me. So i would like the value put into a cell.

The conditional formatting also finds <>"A" in column A and "L" in column C and then looks for the lowest value in column D. It then highlights these cells.

The total value (Sum) of these highlighted cell are of interest to me. So i would like the value put into a cell.

hope that explains it?

[ragulduy]

Do you need to see which cells of highlighted or you just want the total?

If just the total - ignore all the conditional formatting, loop through the cells and test if true or false with your conditional format rule and then add all the ones that are true.

[jetset1]

Hi yudlugar,

Just the total would be okay - would be great if the conditional formatting worked but doesnt look like it
How would i apply this ?

[ragulduy]

=MAX(IF($C$2:$C$20="H",IF($A$2:$A$20="A",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="B",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="C",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="D",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="E",$D$2:$D$20)))

It's an array formula so confirm with ctrl+shift+enter.

[jetset1]

Hi yudlugar,

I dont think this is quite right.

I should end up with 2 answers

52

and

176 from (54 + 33 + 45 + 44)

[ragulduy]

I gave you 52+176 I think, so you want:
=MAX(IF($C$2:$C$20="H",IF($A$2:$A$20="A",$D$2:$D$20)))

and

=MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="B",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="C",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="D",$D$2:$D$20)))+MIN(IF($C$2:$C$20="L",IF($A$2:$A$20="E",$D$2:$D$20)))

[jetset1]

Very nice yudlugar! Thank you kindly