Chang Color of Cell based on cell text

gottimd · Jan 28, 2005

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?

gottimd · Jan 28, 2005

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.

Zack Barresse · Jan 28, 2005

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.

gottimd · Jan 28, 2005

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?

gottimd · Jan 31, 2005

Zack Barresse · Jan 31, 2005

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...

Option Explicit
Option Compare Text

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"
 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
 Case Else
 For n = 3 To 8 Step 5 'cols C and H
 For i = 26 To 191 Step 11
 Cells(i, n).Interior.ColorIndex = 22 'set color
 Cells(i + 3, n).Interior.ColorIndex = 13 'set color
 Cells(i + 4, n).Interior.ColorIndex = 14 'set color
 Cells(i + 7, n).Interior.ColorIndex = 44 'set color
 Next i
 Next n
 End Select
 End If
 Application.EnableEvents = True
End Sub


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

 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

Is this more what you're looking for?

gottimd · Jan 31, 2005

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?

Zack Barresse · Jan 31, 2005

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.

gottimd · Jan 31, 2005

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

Zack Barresse · Jan 31, 2005

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?

gottimd · Jan 31, 2005

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.

Chang Color of Cell based on cell text

gottimd

Well-known Member

gottimd

Well-known Member

Excel Facts

Zack Barresse

MrExcel MVP

gottimd

Well-known Member

gottimd

Well-known Member

Zack Barresse

MrExcel MVP

gottimd

Well-known Member

Zack Barresse

MrExcel MVP

gottimd

Well-known Member

Zack Barresse

MrExcel MVP

gottimd

Well-known Member

Similar threads

Forum statistics

Share this page

Chang Color of Cell based on cell text

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock