Need To Modify This Code - Still Learning

torweb said:

Also, is there some type of index of available colors. I have the code that will return the number of a cell's color but would like to find more color options.

Click to expand...

Well for this bit of your question I can help - run on a blank sheet:


HTH

Yes, you can change the range of cells being impacted. Alter the arguments inside your Range() statements. Sorry, but unless you are more specific as to how you want to alter the impact it's hard to say how to alter your code. :wink:

And while there's nothing wrong with Iridium's code, you might like this: it paints the cells in the same layout as the fill / font color button pallets. Like Iridium's, best you run this in a new WB or at least a new WS.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> PaintThePallet()
    i = Array(Array(1, 53, 52, 51, 49, 11, 55, 56), _
              Array(9, 46, 12, 10, 14, 5, 47, 16), _
              Array(3, 45, 43, 50, 42, 41, 13, 48), _
              Array(7, 44, 6, 4, 8, 33, 54, 15), _
              Array(38, 40, 36, 35, 34, 37, 39, 2))
    
    f = Array(Array(2, 2, 2, 2, 2, 2, 2, 2), _
              Array(2, 1, 2, 2, 2, 2, 2, 2), _
              Array(2, 1, 1, 2, 1, 2, 2, 1), _
              Array(2, 1, 1, 1, 1, 1, 2, 1), _
              Array(1, 1, 1, 1, 1, 1, 1, 1))
    
    <SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> 4
        <SPAN style="color:#00007F">For</SPAN> k = 0 <SPAN style="color:#00007F">To</SPAN> 7
            <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Cells(j + 1, k + 1)
                .Interior.ColorIndex = i(j)(k)
                .Value = i(j)(k)
                .Font.ColorIndex = f(j)(k)
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> k
    <SPAN style="color:#00007F">Next</SPAN> j

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Sure, try the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> ColorRange()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> Sheet1.[g11:g200]
<SPAN style="color:darkblue">If</SPAN> cl.Row / 2 = cl.Row \ 2 Then _
    cl(1, -4).Resize(1, 12).Interior.ColorIndex = 36
<SPAN style="color:darkblue">Next</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

The original was actually written to stop at the last used cell in G, apparently you have a blank column. Also, you could easily use CF for this...

NateO

Pasted in your code on another worksheet and changed the range but get an error. Pasted code is:

Sub ColorRange()
Dim cl As Range
Application.ScreenUpdating = False
For Each cl In Sheet1.[B11:M266]
If cl.Row / 2 = cl.Row \ 2 Then _
cl(1, -4).Resize(1, 12).Interior.ColorIndex = 19
Next
Application.ScreenUpdating = True
End Sub

Hello,

Yes, cl(1,-4) won't work in column B, this tells Excel to look 5 columns to the left, the max you can go is one, i.e., cl(1,0).

Also, change Sheet1 to your sheet code name, or use an index, e.g., sheets(1), etc... whatever the target sheet is.

Also, this code belongs in a Normal module.

Also, just run it in one column eh. It's a multi-column highlighter.

NateO - Thanks...I caught that later...

Also..Thanks Greg and Iridium for your help...that color guide worked great