Chang Color of Cell based on cell text

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
I know that I can do Conditional Formula's but I just have 30 different values that the cell text could be, so I was wondering if there was a way to write that in code? It is also used for 30 different varying locations based on refreshed data.

For an example, in Column A, Rows 2-31, will be different city names, which will vary when the data is sorted each time the revenue's are updated. I wanted the Colors of these cells to change based on what the city name is, but there are too many possibilities to use a conditional formula.

One week it maybe this:

Column A
City X= Black
City Y= Red
City Z= Orange
City A= Yellow
City B= Purple

The next week it could be this:

Column A
City A= Yellow
City X= Black
City B= Purple
City Y= Red
City B= Purple

Does this make sense and can it be done?
 
firefytr said:
Hi there,

This Knowledge Base entry shows how to do this with code ..

http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

It has complete instructions, description, how to use and how to test instructions. It also has a downloadable working example. Post back if you need more help.

HTH

This seemed to work, but it didn't pick up the city names because they are apart of formula's, but with the example from the website, my numbers all of color on them now, so I think I may want to go this route. Can you explain why this code isn't picking up The city names which are results from a vlookup formula. I tried to put Case "Portland" into one of them, and it didn't work.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Set the cell that fires the event as the precedent of the VLOOKUP function. Basically, whichever cell will change first prior to your formulas, set that as the change catalyst.
 
Upvote 0
Okay, so if in Cell C26, the formula is
=vlookup(a2,M1:AQ500,12,FALSE)&" "&"Results"

Set the catalyst as A2? But how will that change the color of Cell C26, and how do I set that cell as the catalyst? The above code has all Active Cells as the case I believe. But it still doesn't pick up the text value that the vlookup formula results in.

Code:
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     
    Dim Cell As Range 
    Dim Rng1 As Range 
     
    On Error Resume Next 
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) 
    On Error Goto 0 
    If Rng1 Is Nothing Then 
        Set Rng1 = Range(Target.Address) 
    Else 
        Set Rng1 = Union(Range(Target.Address), Rng1) 
    End If 
    For Each Cell In Rng1 
        Select Case Cell.Value 
        Case vbNullString 
            Cell.Interior.ColorIndex = xlNone 
            Cell.Font.Bold = False 
        Case "Tom", "Joe", "Paul" 
            Cell.Interior.ColorIndex = 3 
            Cell.Font.Bold = True 
        Case "Smith", "Jones" 
            Cell.Interior.ColorIndex = 4 
            Cell.Font.Bold = True 
        Case 1, 3, 7, 9 
            Cell.Interior.ColorIndex = 5 
            Cell.Font.Bold = True 
        Case 10 To 25 
            Cell.Interior.ColorIndex = 6 
            Cell.Font.Bold = True 
        Case 26 To 99 
            Cell.Interior.ColorIndex = 7 
            Cell.Font.Bold = True 
        Case Else 
            Cell.Interior.ColorIndex = xlNone 
            Cell.Font.Bold = False 
        End Select 
    Next 
     
End Sub

If I set the code to say in one of the lines:
Code:
        Case "Portland"
            Cell.Interior.ColorIndex = 6 
            Cell.Font.Bold = True

It doesn't change the color of the cell that is Portland. I have a recalculate button in there so that button changes the data, but the color still doesn't happen in the Cell C26 in this case if it happens to result in the value of "Portland" from the vlookup. It does work for the single lines where the code is "Case 2", where 2 is the value of the vlookup. Why does it not pick up the Text Value of the Vlookup, but does change the colors if it is actually a numeric character?
 
Upvote 0
Okay, let's take a look at a couple of things.

In the below ..
City Name Change will effect color in ell C26,which will effect color in C29 and effect C30 thru C33 Then
.. I think you meant C32?? That would make it the same pattern as the rest.

If so, maybe this will work for you? I'm assuming they are all based off of the value in A2...

<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Compare</SPAN> <SPAN style="color:#00007F">Text</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, tRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$2" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'<<== CATALYST</SPAN>
        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
        tRow = Target.Row
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target
        <SPAN style="color:#00007F">Case</SPAN> "criteria 1"
            <SPAN style="color:#00007F">For</SPAN> n = 3 <SPAN style="color:#00007F">To</SPAN> 8 <SPAN style="color:#00007F">Step</SPAN> 5 <SPAN style="color:#007F00">'cols C and H</SPAN>
                <SPAN style="color:#00007F">For</SPAN> i = 26 <SPAN style="color:#00007F">To</SPAN> 191 <SPAN style="color:#00007F">Step</SPAN> 11
                    Cells(i, n).Interior.ColorIndex = 5 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 3, n).Interior.ColorIndex = 6 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 4, n).Interior.ColorIndex = 7 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 7, n).Interior.ColorIndex = 8 <SPAN style="color:#007F00">'set color</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> i
            <SPAN style="color:#00007F">Next</SPAN> n
        <SPAN style="color:#00007F">Case</SPAN> "criteria 2"
            <SPAN style="color:#00007F">For</SPAN> n = 3 <SPAN style="color:#00007F">To</SPAN> 8 <SPAN style="color:#00007F">Step</SPAN> 5 <SPAN style="color:#007F00">'cols C and H</SPAN>
                <SPAN style="color:#00007F">For</SPAN> i = 26 <SPAN style="color:#00007F">To</SPAN> 191 <SPAN style="color:#00007F">Step</SPAN> 11
                    Cells(i, n).Interior.ColorIndex = 3 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 3, n).Interior.ColorIndex = 4 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 4, n).Interior.ColorIndex = 9 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 7, n).Interior.ColorIndex = 10 <SPAN style="color:#007F00">'set color</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> i
            <SPAN style="color:#00007F">Next</SPAN> n
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">For</SPAN> n = 3 <SPAN style="color:#00007F">To</SPAN> 8 <SPAN style="color:#00007F">Step</SPAN> 5 <SPAN style="color:#007F00">'cols C and H</SPAN>
                <SPAN style="color:#00007F">For</SPAN> i = 26 <SPAN style="color:#00007F">To</SPAN> 191 <SPAN style="color:#00007F">Step</SPAN> 11
                    Cells(i, n).Interior.ColorIndex = 22 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 3, n).Interior.ColorIndex = 13 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 4, n).Interior.ColorIndex = 14 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 7, n).Interior.ColorIndex = 44 <SPAN style="color:#007F00">'set color</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> i
            <SPAN style="color:#00007F">Next</SPAN> n
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

You can just copy/paste more criteria and change the color if you desire. The copy/paste would be using this code ...

<font face=Tahoma New>        <SPAN style="color:#00007F">Case</SPAN> "criteria 1"
            <SPAN style="color:#00007F">For</SPAN> n = 3 <SPAN style="color:#00007F">To</SPAN> 8 <SPAN style="color:#00007F">Step</SPAN> 5 <SPAN style="color:#007F00">'cols C and H</SPAN>
                <SPAN style="color:#00007F">For</SPAN> i = 26 <SPAN style="color:#00007F">To</SPAN> 191 <SPAN style="color:#00007F">Step</SPAN> 11
                    Cells(i, n).Interior.ColorIndex = 5 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 3, n).Interior.ColorIndex = 6 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 4, n).Interior.ColorIndex = 7 <SPAN style="color:#007F00">'set color</SPAN>
                    Cells(i + 7, n).Interior.ColorIndex = 8 <SPAN style="color:#007F00">'set color</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> i
            <SPAN style="color:#00007F">Next</SPAN> n</FONT>


Is this more what you're looking for?
 
Upvote 0
Thanks! actually A2 isn't the only cell changing, but can I just insert that line as such:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, n As Long, tRow As Long
    If Target.Address = "$A$2" Then '<<== CATALYST
        Application.EnableEvents = False
        tRow = Target.Row
        Select Case Target
        Case "criteria 1"
            For n = 3 To 8 Step 5 'cols C and H
                For i = 26 To 191 Step 11
                    Cells(i, n).Interior.ColorIndex = 5 'set color
                    Cells(i + 3, n).Interior.ColorIndex = 6 'set color
                    Cells(i + 4, n).Interior.ColorIndex = 7 'set color
                    Cells(i + 7, n).Interior.ColorIndex = 8 'set color
                Next i
            Next n
        Case "criteria 2"
            If Target.Address = "$A$5" Then '<<== CATALYST
            For n = 3 To 8 Step 5 'cols C and H
                For i = 26 To 191 Step 11
                    Cells(i, n).Interior.ColorIndex = 3 'set color
                    Cells(i + 3, n).Interior.ColorIndex = 4 'set color
                    Cells(i + 4, n).Interior.ColorIndex = 9 'set color
                    Cells(i + 7, n).Interior.ColorIndex = 10 'set color
                Next i
            Next n

Would that work?
 
Upvote 0
If you are going to do that, I might suggest incorporating the row number into the loop - if the cells your coloring are going to adjust with the rows in column A. You have a lot of cells to change, which is the reason for the loop (and they're sequential).

So I guess it depends on what changing A5 affects. Could go a variety of ways.
 
Upvote 0
The Target address that will "trip" the code change in each sequence is:

C26,which will effect color in C29 and effect C30 thru C32 Then
C37,which will effect color in C40 and effect C41 thru C43 Then
C48,which will effect color in C51 and effect C52 thru C54 Then
C59,which will effect color in C62 and effect C63 thru C65 Then
C70,which will effect color in C73 and effect C74 thru C76 Then
C81,which will effect color in C84 and effect C85 thru C87 Then
C92,which will effect color in C95 and effect C96 thru C98 Then
C103,which will effect color in C106 and effect C107 thru C109 Then
C114,which will effect color in C117 and effect C118 thru C120 Then
C125,which will effect color in C128 and effect C129 thru C131 Then
C136,which will effect color in C139 and effect C140 thru C142 Then
C147,which will effect color in C150 and effect C151 thru C153 Then
C158,which will effect color in C161 and effect C162 thru C164 Then
C169,which will effect color in C172 and effect C173 thru C175 Then
C180,which will effect color in C183 and effect C184 thru C186 Then
C191,which will effect color in C194 and effect C195 thru C197 Then
H26,which will effect color in H29 and effect H30 thru H33 Then
H37,which will effect color in H40 and effect H41 thru H43 Then
H48,which will effect color in H51 and effect H52 thru H54 Then
H59,which will effect color in H62 and effect H63 thru H65 Then
H70,which will effect color in H73 and effect H74 thru H76 Then
H81,which will effect color in H84 and effect H85 thru H87 Then
H92,which will effect color in H95 and effect H96 thru H98 Then
H103,which will effect color in H106 and effect H107 thru H109 Then
H114,which will effect color in H117 and effect H118 thru H120 Then
H125,which will effect color in H128 and effect H129 thru H131 Then
H136,which will effect color in H139 and effect H140 thru H142 Then
H147,which will effect color in H150 and effect H151 thru H153 Then
H158,which will effect color in H161 and effect H162 thru H164 Then
H169,which will effect color in H172 and effect H173 thru H175 Then
H180,which will effect color in H183 and effect H184 thru H186 Then
H191,which will effect color in H194 and effect H195 thru H197 Then
 
Upvote 0
But I was under the impression that C26 and those were formulas? You need to use the cells that are their precedents, such as A2. Would that then make it A2, A13, A24, etc?
 
Upvote 0
C26, and all of the other cells I just listed first are all formula's but they don't depend on one cell. C26 is a result of a lookup from another cell, c37 is a lookup from another cell, etc. Each one of those cells listed in my previous post (The first cell listed) is going to be a text result of a lookup from another cell.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,111
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top