I am trying to determine the best way to change the background color of a named cell range based on the value or color in another cell on a different worksheet. Example: Change color of square tiles (named ranges) on Owners Grid Sample, based on the imported Y column value in the Owners Directory Sample.I have used conditional formatting so that when I change the value in the “Y” column (Rental Agent Key Color Cell) in the Directory it changes the color of the whole line. I would like it to also change the color of the particular named cell references on the Grid in different worksheet.
Example; 4901 Cinzia tile to green based on the imported “NBP.
Each block is a named range.
There are 6 Key colors which are based on the information in column “Y”(Rental Agent Key Cell).
The 6 keys are:
You can choose whatever colors you want. If you do not know what the cell color is then highlight a cell and fill it with color. Then run the Test_Color macro provided. Write down that number. It is the number of the color in that cell. View the picture to see what colors I chose for this example.
The Test_Color macro will not work on fill colors that were generated using conditional formatting. Sorry!
If you choose a cell that is conditionally formatted and run the Test_Color macro the results will be number 16777215 which is white.
Once you import the data from the Grid to the Blocks, run the macro “Colorit” to colorize it by the 6 key above.
Sub test_color() MsgBox ActiveCell.Interior.Color End Sub
The code below should be run from the sheet that contains the Blocks.
Sub colorit() Dim ws2 As Worksheet Dim colr As Long Dim x As Integer Dim i As Integer Dim p As Integer Dim y As Integer Dim z As Integer Set ws2 = Worksheets("Sheet2") x = 1 y = 2 z = 4 For p = 1 To 4 For i = 1 To 5 If Cells(z, y) = "NBP" Then colr = 4697456 If Cells(z, y) = "VRBO" Then colr = 1137094 If Cells(z, y) = "NR/PR" Then colr = 11389944 If Cells(z, y) = "Elliot" Then colr = 65535 If Cells(z, y) = "I-Trip" Then colr = 14395790 If Cells(z, y) = "Vantage Realty" Then colr = 14998742 Range("Block" & x).Interior.Color = colr x = x + 1 'r = r + 1 z = z + 4 Next i y = y + 1 z = 4 Next p End Sub