# Excel 2003 - The fabled more than 3 conditional formatting



## seno_b (Jun 6, 2012)

Hi all,

I know this has been discussed before, but I have not been able to find a solution for my exact situation (as I do not understand VBA at all).

In an excel 2003 sheet I already have 3 conditional formatting conditions set up and require more. I need to make cells in column F turn Magneta (the cell, not the text) whenever the row in column F displays "Priority".

Assistance would be appreciated very much so! I wish I could used excel 2010 but my company is behind the times. 

Thanks in advance!


----------



## seno_b (Jun 6, 2012)

No one knows?


----------



## hobblyhoy (Jun 13, 2012)

Open up VBA in your excel sheet and add this:



> Private Sub Worksheet_Change(ByVal Target As Range)
> Set colorchanger = Range("F1:F1000")
> For Each Cell In colorchanger
> 
> ...


Disable the conditional formating and instead fill in the "other1", "other2" etc areas with what the text you want it to trigger on. Here is a list for the basic colour options:
Color Palette and the 56 Excel ColorIndex Colors


----------



## seno_b (Jun 6, 2012)

Message deleted.


----------



## seno_b (Jun 6, 2012)

Thanks Hobbly!

The code seems to work but I'm running into issues because I already have another VBA code which starts with "Private Sub Worksheet_Change(ByVal Target As Range)", above where I am placing your code.

So then when I go back to the spreadsheet and try to change something, your one is coming up as "Compile Error: Ambiguous name detected: Worksheet_Change"

Is there a way around this? I'm sorry I have absolutely no clue with VBA!

Thanks again


----------



## hobblyhoy (Jun 13, 2012)

put that code in the same area.

So if right now it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[your code here]
End Sub

Make it look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[your code here]
[other code here]
End Sub

So basically copy and paste my code without the first and last line and put it under "Private Sub Worksheet_Change(ByVal Target As Range)"


----------



## seno_b (Jun 6, 2012)

Hobbly, thanks a lot, that's working now. I had tried to insert the two codes together but I think I had done it the other way around (stupidly) so the "Next" had not been between them.

But I have to push the friendship and ask one more thing. I actually need the corresponding row in column C to change when the cell in column F shows as "Priority". Would you know how to add this step in?

Sorry to be a nuisance, but you've been a very big help on a problem I've played around with over months now! Much appreciated!


----------



## hobblyhoy (Jun 13, 2012)

Put 

Cell.Offset(0,-3) = "1"

underneath:

If Cell.Value = "Priority" Then

To place a "1" in the corresponding "C" cell of the changed "F" cell.


----------



## seno_b (Jun 6, 2012)

Excellent! All done. I had been trying to get something working for this for ages. Thanks Hobbly.


----------



## kosh87 (Sep 28, 2012)

hobblyhoy said:


> Put
> 
> Cell.Offset(0,-3) = "1"
> 
> ...


Hi I am having a similar problem to seno_b.

The first part of the code works great for my application, however the 

cell.Offset(0,-3)

part doesn't work for my application.

AS I need to look at the cell with say "Priority" on and fill in the background colour of any >0 values in the rows ("V:BW") of the row with the matching statement.

I was hoping you could help me to solve this.

Thanks.


----------

