Kashif has attended:
Excel Advanced course
Conditional Formatting
Hi there,
I have created a top 10 colour palette for my gross figures by using a formula for top 1 in green, top 2 in lighter green, top 3 in an even lighter green and so on, so that I have ten different colours for that range.
Is there a way to apply these rules to every second row? I have a market share percentage under the gross figures which I want to avoid falling into the conditional formatting.
I hope that's clear.
Many Thanks,
Kashif
RE: Conditional Formatting
Hi Kashif,
Thank you for the forum question.
To apply Conditional formatting to every second row you can use the formulas below.
=AND(MOD(ROW(),2)=0,RANK(A2,$A$2:$A$79)=1) "Dark Green"
=AND(MOD(ROW(),2)=0,RANK(A2,$A$2:$A$79)=2) "Green"
=AND(MOD(ROW(),2)=0,RANK(A2,$A$2:$A$79)=3) "lighter Green"
You will need to amend the formulas to your range.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Conditional Formatting
Hi Kashif,
I tested the formulas and I can see, that I have to rethink my answer to you.
I will be back with a better solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Conditional Formatting
No worries, Jens. Thanks for your time.
To make my query a bit more concise, all the relevant grosses are in one row, I just want to apply the ten rules to every second row.
I can use the format painter but given there's over 100 rows, I wanted to know going forward if I could use a formula to make it faster.
Many Thanks,
Kashif
RE: Conditional Formatting
Hi Kashif,
You can select every second row and then apply the rule or as you suggested use the format painter.
I have got the formulas correct, but as you can see it is not a simple solution.
If you select all cell in the column you want to highlight and use the option in Conditional Formation "Use a formula to determine cells to highlight you can type the formulas below
=If(A2="","",IF(MOD(ROW(A2)-ROW(A$2),2),"",SUMPRODUCT(--(MOD(ROW($A$2:$A$110)-ROW(A2),2)=0),--(A2 < $A$2:$A$110))+1))=1
Click format and select a dark green.
=If(A2="","",IF(MOD(ROW(A2)-ROW(A$2),2),"",SUMPRODUCT(--(MOD(ROW($A$2:$A$110)-ROW(A2),2)=0),--(A2 < $A$2:$A$110))+1))=2
Click format and select a green.
=If(A2="","",IF(MOD(ROW(A2)-ROW(A$2),2),"",SUMPRODUCT(--(MOD(ROW($A$2:$A$110)-ROW(A2),2)=0),--(A2 < $A$2:$A$110))+1))=3
Click format and select a lighter green
And continue 7 more times.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
