VBA Conditional Formatting Colour Coded Custom List

adamhoff

New Member
Joined
May 2, 2019
Messages
1
Hi everyone, I could not find a solution for this.

On Sheet 1 I have a table such as this:


Vehicle
Car
Colour Code
Red
BicycleBlue
TricycleYellow
MotorcycleYellow
TrainWhite
PlaneGreen
BoatAqua

<colgroup><col><col></colgroup><tbody>
</tbody>

On Sheet 2 I have another table such as this:

VehicleCostRentalTickets
Motorcycle25000YesNo
Plane2000000NoYes
Bicycle200YesNo
Car50000YesYes

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>


I am looking for a VBA code which will look up the colour code in sheet 1, and apply the colour listed to the cell fill colour under each cell in the Vehicle column of sheet 2.

I have used VBA for conditional formatting, but with very few criteria, in my real example, there may be up to two dozen different "vehicles" in the colour code sheet.

Thanks for any help getting this in the right direction.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,731
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Here is one way of doing it. Add a third column to your table on Sheet1 that has the VBA color code you want to use.
You can easily get these by turning on the Macro Recorder, and recording yourself changing the fill color of a cell.
You will get code that looks something like this:
Code:
Sub Macro1()
'
' Macro3 Macro
'

'
    Range("C2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = [COLOR=#ff0000][B]255[/B][/COLOR]
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub
Note the number listed after ".Color = ". This is the number you want to put in that third column.

Then, I find it easiest to name this three column range. In this example, I am naming it "rngColor".
To see how to name ranges, look here: https://www.contextures.com/xlNames01.html

Then, assuming that your data on Sheet2 starts in the upper left corner (cell C1), here is the VBA code you can use to color those rows:
Code:
Sub MyColorMacro()

    Dim lr As Long
    Dim r As Long
    Dim ccode As Long
    
    Sheets("Sheet2").Activate
    
'   Find last row in column A on Sheet2
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in column A on Sheet2, starting at row 2
    For r = 2 To lr
'       Look up color code
        ccode = Evaluate("VLookup(" & Cells(r, 1).Address & ", rngColor, 3, 0)")
'       Apply color to whole row
        Rows(r).Interior.Color = ccode
    Next r

End Sub
This should do what you want when you run it.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,075
Messages
5,628,498
Members
416,322
Latest member
Corbett

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
Top