format cell color based on another cells value

[pongmeister]

I asked this before and was told to used conditional formatting however I cannot because I have up to 12 conditions and excel 2003 only accepts 3 in conditional formatting, however I found the following code via google that someone posted that would change cell color based on values.

Please Login or Register  to view this content.

The google question was "Hi, I need to automate the colouring of cells in F:F, G:G, and H:H when the individual cell value equals a certain value (for the example sake let's say if it's closed a Time and Date will fill these cells, if it is not closed the cell is blank or filled with a hifen ("-"), I need to colour the "open" cells)."

MY problem is I don't know VBA well enough to change the code and apply it to my situation - Here's an example of what I want to do:

If value in A1 = 1 set color in B1 to green
A1 = 2 set color in B1 to orange
etc

I would need to test about 12 values and set to 12 different colors. In the above code and question I don't know what is meant by a closed cell. and I don't know what cells the code is evaluating.

Could someone help me to adapt this code to my example above?

Thanks much!!

[LeoTaxi]

Columns 6,7 and 8 are columns F,G and H
and each cell means all cells in those columns

Kind regards
Leo

[Aardigspook]

Hi,

Here's some other code you could use (based on something I used to use before my company moved on from 2003):

Please Login or Register  to view this content.

I've only done the first two figures/colours, but hopefully you can see how it works.

Good luck.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[JohnTopley]

Another .. variant on your posting. The ColorIndex settings are just random.

Please Login or Register  to view this content.

[pongmeister]

Thanks for the explanation - I will experiment in adapting to what I want.

[pongmeister]

Thanks so much and I will try it out.

[pongmeister]

Thanks - I will try this option also - Appreciate all the expert help.

[pongmeister]

Hi Aardigspook,

I have entered the adjustments to your code and am getting no visable results. For background - The only macro's I have created were done via the macro recorder, so i don't know if I'm entering the code correctly or how to envoke the code. Here's the steps I've done.

I selected tools>macros>visual basic editor and entered this version of your example code:

Please Login or Register  to view this content.

Then I selected 'Debug>Compile VBA Project

*** Initially I didn't have the "End Sub" line and it gave an error - then I added "End Sub" and reran the compile and it didn't give an error message or a successful compile message.

Nothing happened with my color scheme so can you help me to know how to envoke the code???

Thanks, much apprecitated

[JohnTopley]

In VBA, double click on the worksheet that you want to add the code to : this should bring up a blank form . Copy the code into this form.

Go to your worksheet and type 1 into AB3: cell AE3 will be green. Type 4 and cells AE3 will be orange.

See attached.

[Aardigspook]

Sorry - I should have explained that the code was worksheet code, not code for a module. Follow JohnTopley's instructions and you should be fine.

@JohnTopley - thanks for picking up the small ball I dropped...

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[JohnTopley]

No problem: returning the help you have given me previously.

[pongmeister]

Hello John,

Yes, that is working ideally - Thanks so much for your expertise!! I will close this thread now

[Aardigspook]

Glad to see you got what you needed and that I/we could help.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.