Paint a cell a color if its value is in a range of cells

[guinch]

I tried to do this with conditional formatting but I cannot seem to get it to work with the next step of my project.

I need to check if a cells value is in a range of cells. If it is then the cell should be red. This was easy enough with CF. However I then need to check if another cell in the same row where the value is found int the range = a certain value and if so paint it amber instead.

If it helps I'll explain the project a little. Its for capacity planning in a datacenter. So each sheet represents a server rack and will have a list of ports on an Ethernet patch panel (A-1 through A-24 and B-1 through B-24). There is also a row for each server section (U) in the rack. In the table I would enter the port used for that server in the appropriate rack row and the relevant cell for the patch panel would turn red to indicate is it used.

This is relatively simple. However I also need book space for servers that are at the planning stage. In this case I would fill in the details of the ports used in the appropriate row and in another column of that row enter the value "Cap" to indicate is it at the capacity planning stage and not physically installed. So in this case the range should be checked for a value and if it exists the Cap column should then be checked, if it is empty the appropriate patch panel cell should be red... if it contains "Cap" the appropriate cell should be amber.

Not the easiest to explain so if it doesn't make sense let me know and I'll try again.

Here is a screenshot of the sheet that might help understand what I'm trying to do.

http://imgur.com/idjUg0s

I appreciate any pointers on this. Thanks.

 

[Worf]

Hi
You could use code like this:

Dim c As Range, a, r%, amber As Boolean
Sub Guinch()
Range("i48:t49").Interior.Color = RGB(5, 250, 5)
Range("i52:t53").Interior.Color = RGB(5, 250, 5)
For Each c In Range("i3:j44")
    If Not IsEmpty(c) Then
        amber = False
        If Cells(c.Row, 8).Value = "Cap" Then amber = True
        a = Split(c.Value, "-")
        Select Case a(0)
            Case Is = "A"
                UpdateColour 48
            Case Is = "B"
                UpdateColour 52
          End Select
    End If
Next
End Sub


Sub UpdateColour(sr%)
    If a(1) < 13 Then r = sr
    If a(1) > 12 Then r = sr + 1
    If amber Then
        Cells(r, a(1) + 8).Interior.Color = RGB(220, 140, 40)
    Else
        Cells(r, a(1) + 8).Interior.Color = RGB(250, 5, 5)
    End If
End Sub