• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Participant

      How to change color of named cell range based on value in another cell

      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.

      How to change color of named cell range based on value in another cell

      How to change color of named cell range based on value in another cell

      Asked by Kevin 4 hours ago in Excel: Formatting.
      1 Answers
      Expert

      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:

      NBP

      VRBO

      NR/PR

      Elliot

      Vantage Realty

      I-Trip

      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.

      RE: How to change color of named cell range based on value in another cell

      RE: How to change color of named cell range based on value in another cell

      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
      
      Answered by Hyside2 14 mins ago..