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

DV8

New Member
Joined
Sep 21, 2005
Messages
6
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
Can you provide a list - which colors you want to use and when you want to use them?
 

DV8

New Member
Joined
Sep 21, 2005
Messages
6
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.
 

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
DV8 said:
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

New Member
Joined
Sep 21, 2005
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,638
Members
412,334
Latest member
ExcelForLifeDontHate
Top