Change Font Color in a Range based on One Cells information

[mrgillus]

I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".

Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"

The example file explains it a lot better..

Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...

Thanks again!

[Phil_V]

If you don't mind VB solution you can put this into the Sheet code area, (click on the tab of the sheet and 'View Code' then paste this in)

Please Login or Register  to view this content.

[mrgillus]

Thanks for the prompt reply Phil... I tried to paste this in, however it throws an error on the highlighted line.

Please Login or Register  to view this content.

The error is "Run-time error '1004':
Unable to set the ColorIndex property of the Font Class

[Phil_V]

Strange...

So working on your example workbook;

I download and open it.
Right click on the tab [Sheet1 (2)] and select 'View code' from the popup menu.
Paste the code as given above into the VBA editor window that opens.
Use the Summaries / Description dropdown box to change the value and all works.

Is that the same as you are doing?

[mrgillus]

My apologies as I am actually pasteing it in another work book.. Although both are set up the same way using the same formulas etc.. That is bazaar.. Usually I don't have any problems transferring to my functional workbook from an example..

Is it possible that having other code on the same sheet would cause this to mess up?

Please Login or Register  to view this content.

It is very possible that I have that setup wrong.. ..

If need be I will wipe the data of my functional workbook and upload that instead..

[mrgillus]

Well here is the official- Minus the important stuff that is.. Basically I am trying to get the colors to change in the "List" Tab. You can click on the Description Cell which is in dark green to select between "Description" and "Summary".

I should have just uploaded it this way initially but thought it would be more time consuming..

Let me know what you think..

Thanks

[Phil_V]

You will need to make the following changes, as you can't change the Font's ColorIndex if the sheet is protected which is one of the things your other macro is doing

Please Login or Register  to view this content.

Hopefully that will sort it out

[mrgillus]

Right.. Let me give that a try.. I definitely didn't think about that..

[mrgillus]

Yup.. Now it work's perfectly.. Maybe you can reference my Example file for the question where I want to change the Title to Blue if the corresponding description in the row says "Click on Title to Follow Link"? Thanks for your help..

I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".

Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"
The example file explains it a lot better..

Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...

Thanks again!

[romperstomper]

This isn't actually relevant to your question, but I would recommend assigning that password to a variable (or constant) and then using that variable in your Protect/Unprotect code. Makes it easier to change it!

[martindwilson]

try this done with conditional format

[mrgillus]

Yup that does the trick!

Thanks a bunch for your guys' help!

Much appreciated

[Phil_V]

Here is revised code, using a CONST for the password, and also for the colorindex's for easy reuse.

Please Login or Register  to view this content.

One point though;
I've used the text you had in your post;

Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"

but that is NOT what current appears in the cells being checked;

Click Title for Description

I wasn't sure which would be right in the end, so figured I'd point it out and you can correct it as needed

EDIT I like Martin's solution, no worries about macro security then, and might/could be faster

[mrgillus]

Thanks once again guys! This was a great help!

[Phil_V]

Note that there is a small mistake in my code, about 5 lines from the end.
The line:

Please Login or Register  to view this content.

Should read:

Please Login or Register  to view this content.

Otherwise it will adjust the font colour of the cells in Column D instead of in C