VBA to change cell color (Cond. Formatting not an option)

[DV8]

I am working with a production schedule and I would like to flag certain cells (by changing the cell color) if the cells contain certain production color codes. My problem is that I have 15 production colors that I need to flag so conditional formatting is not an option. If you look at the picture here:

you can see that the production color codes are listed in column J. I assume that I will have to use VBA to achieve the desired results. The cells that contain these 15 production colors I would like to flag can all be flagged the same color the important thing is that they stand out. Any help would be greatly appreciated.


Thanks

 

[a7n9]

Can you provide a list - which colors you want to use and when you want to use them?

 

[DV8]

I have a list of 15 production colors that I would like to flag anytime they appear in a cell which would be in the J column and they can be flagged with the same color. These production color codes are meaningless to anyone outside of my work they are:

BL52
BL53
BL54
BR19
GR04
GR05
GR42
GR60
GR68
GR67
MA02
MA04
PU02
OR02
WH01

Thanks for your quick response.

 

[DV8]

Anyone?

 

[a7n9]

Insert this macro in the sheet you're working on.

Select the cells, in which you've these color codes, and run the macro.

<font face=Courier New><SPAN style="color:#007F00">'---------------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">' Procedure : Color_cells</SPAN>
<SPAN style="color:#007F00">' DateTime  : 12/16/2005 13:05</SPAN>
<SPAN style="color:#007F00">' Author    : anandesh</SPAN>
<SPAN style="color:#007F00">' Purpose   : To color cells with specific codes</SPAN>
<SPAN style="color:#007F00">'---------------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Color_cells()

   <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Color_cells_Error
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c
            <SPAN style="color:#00007F">Case</SPAN> "BL52", "BL53", "BL54", "BR19", "GR04", "GR05", "GR42", "GR60", "GR68", "GR67", "MA02", "MA04", "PU02", "OR02", "WH01"
                c.Interior.ColorIndex = 36
            <SPAN style="color:#00007F">End</SPAN> Select
        <SPAN style="color:#00007F">Next</SPAN>
    

   <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
   <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Color_cells_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Color_cells"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Von Pookie]

I have a list of 15 production colors that I would like to flag anytime they appear in a cell which would be in the J column and they can be flagged with the same color.

If it doesn't matter if they are all the same color or not, you *can* use contional formatting. Just put all of the options in one condition.

Select the range and use
Formula is with the formula:
=OR($J1="BL52", $J1="BL53",$J1= "BL54",$J1= "BR19", $J1="GR04",$J1= "GR05", $J1="GR42", $J1="GR60",$J1= "GR68",$J1= "GR67", $J1="MA02", $J1="MA04", $J1="PU02", $J1="OR02", $J1="WH01")

And pick a color in the formatting options.

Edit: Alternately, you could just put those codes on a different sheet and reference the conditional format from there instead of the long OR function:
=ISERROR(VLOOKUP($J1,data,1,0))=FALSE

In this example, I put the values in A1:A15 on Sheet 3--but since you can't refer to another sheet in conditional formatting, I just named the range data and referenced it that way.

 

[DV8]

Many thanks to a7n9 and Von Pooki, I am almost embarrassed at how simple the solutions are. I have a lot to learn.

Thanks
DV8