conditional formatting for background color using VBA?

Question

I got VBA code to populate some T-SQL query data in an Excel file. In that data, one column contains values of Red, Amber, Green and N/A. Now I want background Color for according to those values (Red, Amber, Green and White). How can I do this in VBA?

Edited: I need Like this:

id firstname lastname complaint
1  paul      nixon    RED
2  John      nathon   RED
3  sera      teag     AMBER
4  CLARE     walker   GREEN

Now I want background color for column 'complaint' according to cell value, like if cell value RED I want that background color also RED etc.. in VBA code.

[How to ask a good question](http://stackoverflow.com/help/how-to-ask). Show us what you've got, and we'll help fix it. Not likely you'll get someone to do it for you. — FreeMan, Apr 01 '15 at 12:54
@ FreeMan, thanks for the reply. Just changed a bit. that may helpful you to understand. Thanks — GK1960, Apr 01 '15 at 13:02
What code have you generated? Where are you getting errors in it? Again, probably won't be anyone here willing to write your code for you, but will be willing to help fix a specific issue. — FreeMan, Apr 01 '15 at 13:08

score 1 · Accepted Answer · answered Apr 06 '15 at 17:32

Changing the background color of a cell is simple. Determining what color to change it to is the key step here. If you know that those 4 colors are the only options, I would just pound out the cases and set the colors. If you find this growing to more colors, you may want to define them in a Dictionary and do a lookup instead of the SELECT-CASE construction.

This simple code would work with your example. You will want to define the Range better (probably not "D2:D5") based on your real application and tweak the colors.

Sub ColorWithText()

    Dim cell As Range

    For Each cell In Range("D2:D5")
        Select Case UCase(cell.Value)
            Case "RED"
                cell.Interior.Color = RGB(255, 0, 0)
            Case "AMBER"
                cell.Interior.Color = RGB(255, 191, 0)
            Case "GREEN"
                cell.Interior.Color = RGB(0, 255, 0)
            Case "WHITE"
                cell.Interior.Color = RGB(255, 255, 255)
        End Select
    Next cell
End Sub

Here is a picture of my Excel instance after the code runs. image with colors applied

Hi @Byron Wall, In ***For Each cell In Range("D2:D5")*. How can I set Range D5 value to max, because I really don't know how many rows will come in result. Thanks — GK1960, Apr 06 '15 at 22:33
There are probably better resources that address how to get the last value of a range for various cases. Common patterns include: Range.End(xlDown), Range.SpecialCells(xlCellTypeLastCell), Intersect(Range.EntireColumn, Sheet.UsedRange). The easiest if you are sure you will have more than 2 rows and there are no breaks is `For Each cell In Range(Range("D2"), Range("D2").End(xlDown))`. The .End function mimics hitting CTRL+ on the keyboard in a given direction (e.g. Down). Here is a popular SO thread: http://stackoverflow.com/questions/71180/. — Byron Wall, Apr 06 '15 at 22:44

score -1 · Answer 2 · answered Apr 01 '15 at 13:08

-1

Found this that may help you:
https://technet.microsoft.com/en-us/library/ee692862.aspx

answered Apr 01 '15 at 13:08

Gino

175
3
16

conditional formatting for background color using VBA?

2 Answers2