Applying Multiple Conditional Formatting Rules

[KrisKiel]

I am applying 4 conditional formatting rules to 184 cells. I figured out how to correctly apply the formula and the colors I want but it is going to take forever doing it one at a time (that's 736 rules). Is there a way to do this more efficiently?

The rules for the first cell (O7) are:[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
=Module!$F$2="X" ---> Yellow[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=Module!$G$2="X" [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

---> Blue​

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=Module!$H$2="X" [/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]---> Red[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=Module!$I$2="X" [/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]---> Green[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]_{^{<strike>
</strike>}}[/FONT]
The next cells' (P7, N8, O8, P8, Q8, etc. in a triangle shape ending at 19AB) rules are the same columns and continue down the rows in a sheet titled 'Module'.

 

[etaf]

you select the range that the rules apply to - so NOT 1 rule per cell
1 rule per range of Cells , so you still only need the 4 rules (not 736) in conditional formatting, but you need to select the range you want those rules to be applied

Select the range O7:AB19
then apply the 4 rules

unless i have misunderstood how the 4 rules are to be used

 

[KrisKiel]

Yeah so that might work if the rules # value in it would change when I select the range.

How can I share a screenshot here? I think that would clear up what I'm asking.

 

[etaf]

Yeah so that might work if the rules # value in it would change when I select the range.

not sure i understand

How can I share a screenshot here? I think that would clear up what I'm asking./QUOTE]
this forum does not support attaching files , you need a sharing site like, dropbox, onedrive

 

[KrisKiel]

 

[KrisKiel]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/u/s!Alu77L9eUsQfaTcOzpwb4Vf8MWw

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/u/s!Alu77L9eUsQfaoMLdM7ery_faW0[/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[etaf]

thanks for the images
rather than me having to re-create the spreadsheet , can you post a copy of the spreadsheet , ensure any personal data is anonymised , as this is a public forum and the data is available to anyone
i think we can use a countif() or index/math to colour the triangle

something like
select the range
A5:AD19

then setup 4 rules

For yellow,
=Countifs(Module!$D:$D, A5,Module!$G:$G, "X")>0

However, where you have two "X"'s on the same row - what do you want to colour ?

Not been able to test the formula , as i say dont have the time to re-create the sheets

 

[KrisKiel]

Thank you so much for your help. I actually figured out a (relatively quick) way to do it late last night.

If you’re on the verge of figuring out a formula for it though I’d still really appreciate it. For rows where there are multiple Xs, I’d want the right-most column color to show The triangle is available here:

https://docs.wixstatic.com/ugd/57b2...=GEN-Triangle-Fillable-For-Instruction-1.xlsx

What I did was set the four rules in a new column cell on the Module sheet without the absolute value ($) for the cell number and drag copied to the bottom row. Then copied that column’s formulas into the Triangle sheet and dragged each cell into the triangle.

 

[etaf]

i dont have the module data in the sample - so cant do it
BUT
i would put the rules in conditional formatting in the order Top to bottom = left to right and make sure "stop if true" is NOT ticked
then it would default to the right most value

4 rules
as i mentioned
if you want to add the module data for colours and index value - then i can . still do it