Conditional Formatting based upon format of another cell

[JMorrison619]

Hello,

Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?

Thanks,

Jimmy

 

[mikerickson]

Not directly, but if the other cell's fomatting is set by Conditional Formatting, you could test that condition to set the cell color.

Formatting, applied by either the user or by CF, is not data and Excel won't read it.

 

[JMorrison619]

Not directly, but if the other cell's fomatting is set by Conditional Formatting, you could test that condition to set the cell color.

Formatting, applied by either the user or by CF, is not data and Excel won't read it.

Hmm, that's too bad and a little surprising that this functionality hasn't been built into Excel yet. Seems to me that this could have been a useful feature to many users.

Thanks for the reply.

Jimmy

 

[mikerickson]

Its not surprising since formatting is a lousy way to store data.
For example, is an item in blue more or less important than in green formatting?

Rather than having to remember (6 month since the last time you saw the sheet). Importance data is better stored as numbers.

Adding formatting to usefuly stored data for presentation is functionality that Excel has. But it does not cater to bad design (i.e. using format as data).

(/rant)

 

[10R]

I have been struggling with this as well. While not perfect I have found a work around. For example I have a spreadsheet where I want data in certain fields to match a single number format based upon the resolution I am measuring (e.g. 0.0001 or four places)

I select one cell to enter the Resolution. I enter 0.0001 and all the conditional format cells I have selected in my rules format are formatted to .00001 (data appears as 5.0200, 4.9934 etc)

The down side is you need a formula for every resolution. For example I would have to enter the formula =$E$14=0.0001 You would have to enter different formulas for each resolution required. For example =$E$14=0.01...not very elegant but it works. Then just match the resolution dictated in the formula to the number format selected in the "format" button of the rules.

There is no reason you could't do this with colors as well. It is similar to setting up a lookup table...but not very elegant.

 

[anuj1uch]

Is it possible to link the colour of a cell based on the colours of another cells.
Suppose A1,A2,A3 are all green then A4 will be green
And IF only one of them(A1,A2,A3) is red the A4 is red.

 

[mikerickson]

How do A1, A2, A3 get their color? By user changing the interior color or via Conditional Formatting?

 

[anuj1uch]

How do A1, A2, A3 get their color? By user changing the interior color or via Conditional Formatting?

Through conditional formatting A1,A2,A3 are getting their color. And one more thing to clarify that putting all the conditions in A4 is not possible as A1,A2,A3 are fetched from different source and all have different conditions.

Rgds
Himanshu

 

[mikerickson]

You can put all the conditions onto A4, by using names.

For example, if (A1 is red when Sheet1!$T$1 =5) and (A2 is red when Sheet2!$T$2 < 10) and (A3 is red when 100 < Sheet3!$T$3)
You could define three named formulas
Name: A1Condition RefersTo: =(Sheet1!$T$1 =5)
Name: A2Condition RefersTo: =(Sheet2!$T$2 < 10)
Name: A3Condition RefersTo: =(100 < Sheet3!$T$3)

Then you could use =OR(A1Condition, A2Condition, A3Condition) as a CF formula for A4.

 

[AB123]

Hi there,

Just to add to that, how would you manipulate this if I used a 3-color scale for a column and I wanted to apply only the color across the entire row.

In my spreadsheet, I use a formula to calculate # of days overdue, but only if there is a value in [=IF(ISNUMBER(C4),C4-A4,"")], where A4 and C4 are dates (MM/DD/YYYY).
Then, I use the 3-color scale based on values (min:30, mid:60, max:90).
This works well for the column I originally applied it in, but when I try to change the range to the whole table (which has some other dates, manually entered data, and text) it only applies it to the cells containing dates (assuming because Excel reads them as numbers and will only apply the formatting to numbers because of how I set it up).
Not only that, but it will only format it as the color I set for the highest value (assuming that Excel is reading the dates as numbers (i.e. 42304) which is larger than the 90 condition I put).

Thanks!