Q: Conditional formatting

[Danich]

Not sure how to make the title more descriptive.. sorry...
Not new to Excel but have always used it for straight number-crunching.

I have a list of 15 values stored on sheet 3 in a column; for this purpose call them products. Each product is formatted (background color and font) the way I want it to always appear. That is, if "widget" appears somewhere in the spreadsheet I want that cell to match how the cell containing "widget" appears on Sheet 3. I understand that this won't happen automagically; the cell containing "widget" has to have conditional formatting applied. It's HOW to set up the conditional formatting that is throwing me for a loop. It's a lookup for a format based on value.

I've only ever used simple conditional formatting in the past (if value<0 then background is red, for instance) but this is beyond what I can see how to do. Thanks in advance for any assistance.

[Special-K]

VBA would be the answer here as formulas cant tell the colour/format of a cell but VBA can.

[Danich]

Perhaps I didn't explain it well since your answer (which I do appreciate you taking the time) doesn't match what I'm doing.

In pseudo-code:
If content of CellA matches a cell content in columnX on sheet 3 format CellA to match the format of the matched cell on sheet 3

I'm not trying to lookup formats; looking up data and formatting based on the value found

[Tony Valko]

I'm not trying to lookup formats; looking up data and formatting based on the value found

It sounds like you ARE looking up the format.

In pseudo-code: ...to match the format of the matched cell on sheet 3

Or, perhaps you could give use some examples so we can better understand what you want to do.

[Danich]

Fair enough...
As stated there is a list (which is static and will never change) on sheet 3 of products (widget, left-handed scissors, etc). Each cell is formatted a specific way (not real involved: background color and font).
On Sheet 1 (for instance) I input "widget" in a cell. When I hit enter I want this cell to match the formatting found on Sheet 3 for "widget." So if "widget" on sheet 3 has a yellow background and bold font, the cell containing "widget" on Sheet 1 should format to a yellow background with bold font.
So far not bad but the cell taking the input could take any of the 15 products so it needs a lookup on the value "widget." Doing a lookup to match a value is simple; it's copying the formatting that's got me puzzled.
To emphasize: what is being matched is cell content (widget) not cell formatting (which, as Special-K said, is not possible). It's "find X in this location, if match=true then do Y to the cell containing X based on the cell where the match was found"
Better?
Thank you for your time!

[Tony Valko]

If the cells in your product list use conditional formatting then you can use that same conditional formatting in your lookup cell. Otherwise, this can not be done without VBA.

[Danich]

Okay... I want to avoid VBA if possible. More complexity than I want to deal with (not to mention whoever comes after me).
So, what you're saying is is to use conditional formatting to format the static list on Sheet 3. So each cell would be "if <content> = <product>" format as ABC.
With each cell using a different <content> and <product> (both of which are equal).
Then.... what? Will it automatically carry over to the input cell on Sheet 1 on match=true?

[Tony Valko]

Let's assume Sheet3 A1 = Product1

You set conditional formatting on this cell to use a green fill:

Home tab>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Formula: =A1="Product1"

Click the Format button
Select the Fill tab>select a shade of green>OK out

On some other sheet in cell A1 you might enter Product1.

Apply the same conditional formatting rules to this cell as you did to the cells on Sheet3.

[Danich]

Okay, I can see that.
Yet there are 15 products that could be entered in any order, at any time, on Sheet1. The column in which it could be entered is a static value (specifically C2:C25) AND it can change to any of the 15 products. If "widget" has a yellow background and "scissors" is a green background: enter widget in Sheet1 in that range and on enter the background should go yellow. Go back later and change it to "scissors" and on enter the background should change to green. But it's BASED on the format that exists on Sheet3; next month the background for "widget" may be defined as blue. Ideally I should be able to go to Sheet3, change that one rule to blue and all instances of "widget" get a blue background.
To put it another way: forget formats and focus on the cells containing numbers that are referenced in formulas elsewhere. If I change one of those numbers all cells using formulas that reference that modified cell will change their contents.
I feel like the answer is just inches away but I can't see it.

[Tony Valko]

Formulas can only return a value to a cell. It can be 1 of 4 types of values:

A text value like "Yes"

A numeric value like 100

A logical value like TRUE or FALSE

Or an error value like #N/A

That's it. That's all a formula can do. A formula can not "pull" formatting from another cell.

So, without using VBA, the only way to do what you want is to apply the same conditional formatting to both locations.

[Danich]

Hmm... then I misunderstand the purpose of the last selection when setting up Conditional Formatting. To wit: "Use a formula to determine which cells to format"