Colouring cells using VBA code?

[DonyBoy]

Hello there,
I’m trying to colour some excel cells in a unique way. In column A, I have some names of people. Each person has a unique colour associated with his/her name. So, when I write that persons name in (lets say, cell A5) I want D5:F5 (3 cells) to go blue (or whatever the colour associated with that person). There is a column of 20 ish names and I want this same thing to happen to each row as and when I change/write names in Column A.

Is this possible to do this in Excel without a VBA function? If not, can anyone tell me how to do this in VBA?

 

[DonyBoy]

Also, I forgot to say that cells D5:F5 (or the corresponding row) should be coloured, only if these cells have values 1, 2 or 3 in them.

 

[xlbo]

For lots of examples of colouring cells based on contents, see this thread: 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

 

[AnotherHiggins]

DonyBoy,

Did you look at the link I provided in your original post (thread68-1321004)?

thread68-1295894 (the link I provided) provides examples of both coloring a cell other than the one being tested and a VBA solution to use more than 3 conditions.

I kind of expected questions about how to implement what you read, not just an exact repost of your question that was already answered.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - Donyboy posted this before your reply I think...

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

 

[AnotherHiggins]

Well color me embarrassed. Thanks for the wakeup call, Geoff.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

no worries John - I'll go back to sleep now...

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

 

[dbvogt]

I need to get around the 3 conditions limit of Excel. Thread 68-223068 has lots of good code for this but is aimed at the user entering data into a cell and then the code colors the background. My cells already have formulas in them so I cannot use the code as written - the sheet is basically a schedule of construction project phases. Any suggestions?

 

[SkipVought]

So how would your program know what color goes with what name?

Skip,
[sub]
[red][/red]
[/sub]

 

[dbvogt]

I'm not sure what you mean. There are letters in the cells already due to if statements. I need to find code that will check each cell (or the entire range) and change the background depending on the letter. The following is what is in one typical cell of the schedule: =IF(AND(AS$3>=$N37)*(AS$3<=$O37),"E",IF(AND(AS$3>=$K37)*(AS$3<=$L37),"C",IF(AND(AS$3>=$H37)*(AS$3<=$I37),"U",IF(AND(AS$3>=$AQ37)*(AS$3<=$AR37),"D",""))))

AS3 is the start of a row of months out to 2014, the N and O columns are start and end dates of an environmental survey, K and L are conceptual design dates, etc. If, say January 2007 fell between the K and L dates, a "C" would appear in the January cell. Ditto in the remainder of the months and years. The formulas basically create a bar chart. Dates for environmental are on one row, dates for the other tasks are on separate rows below. I could wrack my brain and go cell to cell with code but I thought there would be an easier way.

 

[SkipVought]

"...My cells already have formulas in them so I cannot use the code as written..."

Yes, but the code will be the basis for what you need to do.

Here are the things you'll need your code to do.

Loop thru the rows of interest

Process each row as required.

So to loop thru each row, we have to know something about the sheet.

What column has a data value in each row? This is often the left-most column of a table.

Are there any row that are EMPTY (with data continuing below)?

The most favorable situation is that the entire table of data is CONTIGUOUS. Otherwise, you have to use extra-ordinary measures to process The Data, The Whole Data and Nothing but The Data.

Skip,
[sub]
[red][/red]
[/sub]