# Thread: Highlighting Cell based on highlighted cells to the right

1. ## Highlighting Cell based on highlighted cells to the right

I have Branch names in column A and Values in columns B to K from row 6 to 25

I would like to set up conditional formatting if possible so that where there are any values that are highlighted to the right of ColumnA for eg if D6 is highlighted, then A6 must also be highlighted

Your assistance will be most appreciated

2. Howard,

A very interesting situation. You are setting the color of these cells via conditional formatting. When I try to test the interior color of any of the cells they all return the value of a normal cell 16777215. However, if I manually set a cell to a color it will return the proper color code, i.e. Red = 192. Go Figure.

P.S. The attached .xlsm file you can run the Test macro to see the results. I tried to create a User Defined Function {IsHighlight} to use in the conditional formatting, but of course that didn't work.

Attachment 28965Attachment 28966

3. Hi RG

Thanks for the reply. The color of the cells in Cols B:K is determined by conditional formatting.

Is is possible to set up a macro or conditional formmatting so that if any cell to the right of column A is highlighted due to conditional formatting, The cell in Col A will also be highlighted?

4. Set up the formula rule:
=MAX(B6:K6/\$B\$33:\$K\$33)>1.05

Steve

5. Just a thought, insted of testing for a colour, test for the lack of colour. Then any cell that fails the test could then cause the cell in column A to become coloured?

6. Paul,

That is what I was trying to do. The long number {16777215} is what is returned by any cell which has not been colored and also those colored via conditional formatting.

Steve,

Nicely done!

Thanks

7. Could you please explain your logic for the rest of us?
Each of the individual cond formats is checking if the cell is > 5% over the value in row 33 (1.05*rpw 33). Thus I checked each value (B6/B33, c6/C33, etc) and checked the maximum. If the max is over 1.05 than at least one column in that row met the individual cond format and should be colored.. [The formula is an Array formula which cond implicitly works with...]

Steve

8. Steve,

Thanks.

9. Hi Steve & RG

Thanks for the help.

The formula supplied by Steve =MAX(B6:K6/\$B\$33:\$K\$33)>1.05 works perfectly

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•