Question

Conditional formatting in excel 2010

Hello,

I have an excel sheet that has font in 3 different colours. Is it possible for me to create a conditional format that changes the background colour of the cells based on the font colours?

Discussion is locked
Follow
Reply to: Conditional formatting in excel 2010
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: Conditional formatting in excel 2010
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Comments
- Collapse -
Answer
Re: conditional format

If the font colors are set manually, I'm afraid you need to write and execute a macro to do that.

Kees

- Collapse -
Thanks

Ok thanks. I'll do it manually. I was just curious to see if it could be done automatically.

- Collapse -
Perhaps a workaround?

I can see what you want and I agree with Kees that Conditional Formatting doesn't use font colour as a cell condition that can be checked. Like Kees said, you could do this with macros.

But there is a possible workaround, depending what the data in the cells are. Does the font colour depend on the contents of the cell? If so, then you can set both the font colour and the background colour depending what the cell contains.

Just a thought.

Mark

- Collapse -
Not possible

Thanks for the idea but it won't work. The font colour depends on which companies data was inputted.

- Collapse -
So the company name is on the sheet so you know this?

Or are you relying on the person changing it manually?

- Collapse -
Not clear

Not sure I understand the question but there is a foot note saying company 1's comments are in blue, company 2's comments are in green and so on. The font colours are not really clear when printing and I was thinking that changing the background colour might be a solution. I was just looking for a way to make the process quicker.

- Collapse -
Ahh, so the data is not on the sheet.

That explained it well enough to me. The process relies on the person making the spreadsheet to change the color and is not derived from data on the sheet.

-> I know it's hard but this is where putting the data into the sheet must include what's needed to create a report sheet that is printed. All the data isn't in the spreadsheet so you can't make conditions that would make this easy.

It may be hard to create this the first time.

- Collapse -
Answer
Re: conditional format

It can't be done with a conditional format. But it's easy to do with a few clicks, and I think you can record a macro to automate it, although I didn't try that. Here we go:

- Home >Search and Select > Find
- In the Search and Replace box click Options, then click Format.
- Specify the font color you wish to give a different background.
- Back with OK.
- Click Search all.
- In the bottom of the dialog window you'll get a list of all cells with that font color. The first one is selected.
- Using the vertical slider, go all down and shift-click the last line. This select all cells that have been found (can be an uncontiguous selection).
- Click on the background color icon in Start (or use the dialog box launcher in the Font group to format the selected cells with the right background color).

But as Bob wrote, it's still easier to instruct the person making the spreadsheet to make it more printer friendly. Then this would be a one-time operation with no need to record a macro.

An interesting lesson, by the way: your subject line was about conditional formatting and with that tool it isn't possible, so the answer was "no". A more open question would have been "How can I give all cells with a certain font color a certain background", or somewhat more general "How can I select all cells with a given font color". If you don't know how to do something, ask if it can be done or how to do it, and don't steer the people wanting to help you in a direction that could be wrong, and in this case was wrong.

Kees

Post was last edited on October 19, 2015 1:55 PM PDT

- Collapse -
Even easier.

Find and Replace. Like this:
- Find all cells with a red font as in the post above.
- Replace the formatting with the background you want.
- Replace all
That's on page 134 of that book: "For example ... you can search for every cell that contains a specific font and modify these cells so they use a new font". In your case that is: "you can search for every cell that has a certain font color and modify these cells so they have another background color."

Only some 700 pages to go in that book, and you know how to use Excel.

Kees

- Collapse -
Thanks

Thank you for your replies. They were really helpful.

Although my topic was correct. I knew there were other ways to solve this problem. I also wanted to know the limits of conditional formatting

Thank you once again.

CNET Forums