Conditional format based on variable names and colors entered

[kartiki]

I have a column created where user enters resource names. There is another column after previous column where user inputs color associated with that resource.
I have a cell range defined which contains resource names from this list and I need to condition format this range for colors specified by user.
How do I do it? either manually or by VBA..
resource list keeps on updating and I need to update the condition format too..

 

[Eric W]

How will the color be entered? Will it be typed in (e.g. "blue"), or will the cell actually have its fill color changed? How many resources will there be? Will any of the colors be duplicated (i.e. use the same color for more than one resource)?

 

[kartiki]

How will the color be entered? Will it be typed in (e.g. "blue"), or will the cell actually have its fill color changed? How many resources will there be? Will any of the colors be duplicated (i.e. use the same color for more than one resource)?

Cell will have the fill color changed.
there would be variable no of resources.. resources would keep on getting added.. but max 25
non of the colors would be duplicated

 

[Eric W]

I can think of 2 ways to do this. If you have a sheet just to define the colors, you can use conditional formatting. Create a new sheet and rename it Colors. Then across the top, make a column for each color you'll use. If I understand your response correctly, there will be at most 25 columns. Underneath each color, put the names of the people who will get that color. Then go back to the column where you want the colors to appear (I'll assume column G for now), select it, select Conditional Formatting --> New Rule . . . --> Use a formula. Then enter

=MATCH(G1,Colors!$B:$B,0)

in the formula box, and select the format to match the color. The G1 should match the column you selected, the $B:$B should match the color column from the Colors sheet. You'll have to repeat this for up to 25 colors, but there's no VBA involved, which matters to some people.

Option 2 involves a VBA macro. I assume your name column is A, the colors is column B, and the column to get the colors is column G. Open your spreadsheet, Open the VBA editor (Alt-F11), double-click on your sheet in the navigation pane on the left (like "Sheet1"), then paste this macro:

Private Sub worksheet_change(ByVal Target As Range)
Dim r As Integer

    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    Target.Interior.Color = xlNone
    
    r = 1
    While Cells(r, "A") <> ""
        If Target = Cells(r, "A") Then
            Target.Interior.Color = Cells(r, "A").Offset(0, 1).Interior.Color
            Exit Sub
        End If
        r = r + 1
    Wend
End Sub

Change the "G:G" on the third line to the column you want to get the colors, change the "A" in 3 places below that to match the column with the names. The column with the colors is assumed to be the one right next to that.

Let me know how it works. Good luck!