Conditional format based on another cell's formatting (eg fill / background color)

[phirefly]

Hello,

I've searched online and not had much luck trying to find a solution for this.

I am not an Excel expert and do not know VB so I was trying to use conditional formatting to format a cell based on another cell's format (fill color)...

• Find cells in range K5:V5 with fill color green [I use the Excel formatting "green fill with dark green text"]
• >Of these cells, select the cell containing the HIGHEST value.
• > > Format the adjacent cell AFTER the selected cell.

 

[6StringJazzer]

If the green fill with dark green text is manual formatting, and not conditional formatting, then you must us VBA to do this. There is no worksheet function that will tell you the fill color or text color in a cell.

 

[phirefly]

If the green fill with dark green text is manual formatting, and not conditional formatting, then you must us VBA to do this. There is no worksheet function that will tell you the fill color or text color in a cell.

Hello @6StringJazzer, thanks for the reply

The green fill is a conditional format.

It is applied to various cells in K5:V5 (not sure of terminology: "non contiguous range"?).

I was hoping to use the color as I thought it might simplify the formula (because the cells are "non contiguous").

 

[6StringJazzer]

You will need to use a formula that includes the condition you are using to make the cells green.

 

[Drrellik]

can you use the same 'Condition' to format the second cell.

 

[phirefly]

Thank you for your input...I'm not sure if I can use that formula/condition. Here are the details...

The formula/condition to make the cells green is applied to each individual "Sell Price" column. Here is the formula for column K (applies to: Stationary!$K$5:$K$999):

• GREEN: =$C5>(K5*$F$3)
• - used to highlight Sell Prices that are a % ($F$3) less than the competitor price ($C5)
• RED: =$C5<($K5)
• - used to highlight Sell Prices that are greater than the competitor price ($C5)

The Profit columns are simply "Sell Price - Cost" (eg. L5=K5-J5)

In English, what I'm trying to do is:
For our price points that are a % ($F$3) less than the competitor price ($C5), highlight the greatest profit. If it simplifies things, I am more than happy that the sell price is just below the competitor sell price (i.e forget the "% less").

I've attached 2 screenshots...

01 Sheet: current formatting

02 Sheet: mock-up of what I would like to see with new conditional formatting

Thanks again!

 

[mingandmong]

Hi phirefly
can i ask what software you used to post you screenshots, as I need to do the same for my workbook

 

[phirefly]

Hi phirefly
can i ask what software you used to post you screenshots, as I need to do the same for my workbook

I used this: https://imgbb.com/ and then took the image url and used the "insert image" button. I guess you could also just paste the BB code it generates.

 

[Fluff]

There are add-ins available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
that will allow you to post samples from your sheet direct to the board. This is better than images as we can then copy paste the data into our own workbooks, rather than typing it out.

 

[phirefly]

There are add-ins available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
that will allow you to post samples from your sheet direct to the board. This is better than images as we can then copy paste the data into our own workbooks, rather than typing it out.

Thanks! I tried the add-on (Mac) without luck - it wanted me to debug.

I've attached the Excel file here http://s000.tinyupload.com/?file_id=88842591652622095736

If anyone can offer any guidance I'd appreciate it (see 13th December post for screenshots and info). Thanks!