Conditional Formatting plus an extra cell

**Elegidos** · 10-27-2009, 08:20 PM

Hi,

I have a sheet with the following conditional formatting:

1. =COUNTIF(used,A1)
2. Format is orange font and black background
3. used = 500
4. Cell C1 has the # 500
5. Cell D1 has text/numbers/etc.

6. Have cell D1 use the same format as C1 no matter what the information in the cell?

Thanks in Advanced

**DonkeyOte** · 10-28-2009, 03:51 AM

Post a sample file - it's hard to understand the significance of the A1 reference in your conditional formatting formula based on your narrative.

**steveljohn8** · 10-28-2009, 05:27 AM

yes excel is good place where you can do a lots of office work and cell formatting is so much easy on it.

**Elegidos** · 10-28-2009, 06:29 AM

OK. Here is the example:

**DonkeyOte** · 10-28-2009, 06:41 AM

I'm still not sure I fully understand the requirements (ie why you're referencing A1 etc...) ...

if it is your intention to format E4:H29 based on content of E4:E29 matching "used" then remove existing conditional formats, highlight E4:H29 (selecting E4 first) and apply new conditional rule of:

=$E4=used
apply format accordingly

you should then find E7:H7, E16:H16 and E25:H25 are formatted as required (altering value of "used" will obviously update the formatted cells in your table)

**Elegidos** · 10-28-2009, 03:46 PM

The example I posted is just a short part of the sheet I have. The orignal sheet is like the following:

1) Sheet1 is filled with numbers, from A1:DE104 (grid of about 100x100)

2) Sheet1 has the conditional formatting of =COUNTIF(used,A1).
Format of orange font and black background.
The conditional formatting above I believe means for the whole sheet.

3) Sheet2, column A with the RANGE NAME of "used."

4) Sheet2, column A - when ever a number is entered into that column, it searches in sheet1 and if found it does the format (orange font and black background)

5) What I want to know if it´s possible to have a conditional formatting like the following. Following example is not a technical conditional formatting:

=COUNTIF(used,A1)
if the above is true use same format for the adjacent cell (cell on the right)

Sorry for any misunderstanding on my question or bad explanations

Thanks

**DonkeyOte** · 10-28-2009, 04:05 PM

Quite a different question in reality.

Well first you need to ensure your Used named range is dynamic to the extent it picks up new values as they are added, eg assuming numerics as implied:

Please Login or Register  to view this content.

With the above setup you can then apply your conditional rule, highlight range Sheet1!A1:DE104 (selecing A1 first) and apply a Formula based rule of:

Please Login or Register  to view this content.

as you add numeric values to Column A on Sheet2 so your matrix on Sheet1 should format accordingly.

Now I'm still not sure if that's what you want, ie:

Originally Posted by Elegidos

=COUNTIF(used,A1)
if the above is true use same format for the adjacent cell (cell on the right)

is still rather vague given you refer to range A1:DE104... by "cell on the right" do you mean the entire row ? (ie if A1 matches a number in used range then format A1:DE1)

Regardless...
Conditional Formatting is regarded as being super volatile, ie used excessively it will probably cause some slowdown in terms of performance. For more info. see Volatile section on Charles Williams' site - link in my sig. (Volatility)