Set a Cell Color Based on Cell Color of a Difference Cell

[madkinson]

I've searched the board and can't quite get the answer I need. If I have a cell that is set to Yellow fill based on a Conditional Formatting rule for that column, I want to be able to set a Conditional Formatting rule for corresponding cells in columns D and F so they also would be Yellow.

For example, I have a rule that colors the cell in column J that has the Bottom 1 value, which ends up being cell J8. I would like to color cells D8 and F8 yellow based on the fact J8 is yellow. I do not know how to write a formula that looks at the cell color instead of its content. Your help with this is greatly appreciated.

 

[madkinson]

I found a very helpful link that lists all the ColorIndex, RGB, and VB colors.

https://access-excel.tips/excel-vba-color-code-list/

 

[Eric W]

No built-in function can detect the background color of a cell. Instead, why don't you just apply the same CF rule that you have in J to the D and F columns?

 

[madkinson]

No built-in function can detect the background color of a cell. Instead, why don't you just apply the same CF rule that you have in J to the D and F columns?

Because they are labels and not values. An extremely simplistic representation of the data is below:

Bidder Material Price
------- --------- -----
Joe Gravel 5.00
Mike Gravel 5.25
Steve Gravel 5.15

The CF will highlight the price of 5.00, but I also want it to highlight Joe and Gravel that are in non-contiguous cells. If it was the entire row, I could get it to work, but not when the cells are not net to each other.

 

[Joe4]

If I am understanding you correctly, it can still be found with Conditional Formatting.

Assuming that column J has the value that you are looking for the minimum, and column D, F, and J are the columns you want to highlight, do the following:
1. Select columns D, F, and J
2. Go to Conditional Formatting and select the Formula option
3. Enter this CF formula: =MIN($J:$J,$J1)=$J1
4. Choose the yellow fill option
5. Click OK

I think that should do what you want. Columns D, F, and J will be highlighted in the row where the minimum value of J occurs.

 

[madkinson]

Joe4 thanks for the response. I tried it out and it worked in that it highlighted cells C, F, and J in the row. However, it did not select the row with the MIN value in J as expected. It picked the second lowest value. I verified that all cells in J are formatted as Accounting. I tried it again while changing the format to Numeric and got the same result.

I tried adding a low value at the end. When I entered the value, the formula highlighted the cells in the row that the cursor moved to instead of the row expected. It's as if the formula offsets by one row.

Am I missing something?

 

[madkinson]

NM. Excel changed the formula when I applied it. I fixed it and now it works. I appreciate the help in getting this resolved.

 

[Joe4]

You are welcome.

The most common cause of that is people entering the incorrect range in the formula. The important thing to remember when entering a CF formula for a multi-row range, is that when you write the formula, you need to enter it as it pertains to the very first cell in the range you have selected to apply it to. Excel will adjust it automatically for the others, as long as you have applied the correct absolure/relative range references.

I commonly see people do the following. The will select whole columns, and then write the formula, referencing row 2 (not 1). There reasoning is that the first row is a header/title row, and they do not want to include that in the Conditional Formatting. But that would cause the behavior you are seeing. In that case, you do not want row 1 in your selected range to apply the Conditional Formatting to, so you cannot select entire columns (or you need to write your formula with row 1 references - you can always add an AND condition, like =AND(ROW()>1,...), which will then ignore row 1, even though you have included it in your range.