Ask a new question

Dynamic Conditional Formatting

I am working with a chart using many rows and I am having difficulty developing formula(e) that I can apply to every cell in a row to achieve the following:

I have two scenarios: in a single row, and a range of columns L thro’ GV.

In any cell in the row

1.      When the letter C is entered, all cells (excluding the cell containing the C) to the left change colour.

2.      When the letter L is entered, all cells (excluding the cell containing the L) to the right change colour.

There will only ever be a single C or L entered in the row. There may be both C & L in the same row but the L will always be to the right of the C.

Any help would be appreciated. 

 

 

Answer
Answer
In L1:V1 (too lazy to go to GV1) I typed some text with "C" in one of the cells. Copied this down 10 rows and change the position of "C" in each row. Selected L1:V10 and used this rule =COLUMN()<COLUMN($K1)+MATCH("C",$L1:$V1,0) with a blue fill. This gave the required results. I thnk you can see how to add the second rule. best wishes
Bernard Liengme, Nova Scotia, Canada http://people.stfx.ca/bliengme/
http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

I set this up using the range C2:R2 for testing. Select that range, with C2 as the active cell, and use this formula for the "C" entry:

=AND(COUNTIF($C2:$R2,"C")>0,COLUMN(C2)<MATCH("C",$2:$2,0))

adjust the references to suit your range, but ensure that you have the $ symbols. Set the format you want for ths condition.

You can use a similar formula for "L" as a second condition, but you will want > rather than < in the second half.

Hope this helps.

Pete

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 1,332 Applies to: