VBA conditional Formatting Excel 2003

[chriscusick]

Hi,

I have a spreadsheet that I need to do 6 conditional formats on. I realize this needs to be done in VBA but I have NEVER used VBA before so I'm at a loss.

What I need the vba to do is check the text in cell A1 and if it meets a certain criteria colour cells a1 - j1 a certain colour. The code needs to change the cells for the relevant criteria.

The other criteria are also in cell a1, in drop down form.

If it helps, the criteria are:

"To be booked", "Booked", "Done", "Invoiced", "Paid", "Cancelled"

So, for example, if cell A1 = "DONE" then cells A1-J1 would turn green. If Cell A1 = "Cancelled" it would color the cells Red, so on and so forth.

Also, this would need to be extended DOWN column A some 150 lines.

Is there anyone who could actually write this out for me as I have NO programming experience in VBA and wouldn't even know where to start adapting one of the examples from other threads.

Thank you all in advance

 

[SkipVought]

hi,

The place to start is

1) forum707

2) turn on your macro recorder and record a) selecting the range in 1 row, b) assign the color/format sucessively for each of your states and then 2) turn off your macro recorder.

3) copy the recorded code and paste into the new thread you will start in forum707. Explain what you need to do in detail. For instance, you stated, "this would need to be extended DOWN column A some 150 lines." Does this mean that you have an existing table that is some 150 rows deep, or does that mean that you currently have a table with less rows than that and it could grow to something longer?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[chriscusick]

Hi Skip,

Thanks for the post. I am sorry it was a little confusing. I will repost this in the forum you linked.

My only question regarding the macro recorder: I did as you stated whilst recording but looking at the code the only thing it seemed to do is change the colours, not specify the status information. Not sure if that was the intended result but i have posted it to the correct forum (linked below).

http://www.tek-tips.com/viewthread.cfm?qid=1709210

 

[mintjulep]

What is "Done". It seems redundant and non-unique.

How is "Done" different from "Paid"?

How is "Done" different from "Canceled"?

 

[xlbo]

Just as an fyi - this is probably the best thread discussin conditional formatting in VBA (>3 conditions):
thread68-223068

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question