How to highlight a cell using the hex color value within the cell?

by

Can’t be achieved with Conditional Formatting for all colours.

Assuming: Row1 contains Data Labels, data set does not have gaps, the HEX colour is for the fill not the font, you have parsed the HEX colour values (numbers, not formulae) into Columns C:E (R,G,B) and that you do not require to do this often, then the ColourCells macro might suit:

Sub ColourCells()
Dim HowMany As Integer
On Error Resume Next
Application.DisplayAlerts = False
HowMany = Application.InputBox _
(Prompt:="Enter last row number.", Title:="To apply to how many rows?", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If HowMany = 0 Then
Exit Sub
Else
   Dim i As Integer
   For i = 2 To HowMany
      Cells(i, 3).Interior.Color = RGB(Cells(i, 3), Cells(i, 4), Cells(i, 5))
   Next i
End If
End Sub

and enter the value you want for n when prompted.

Sample output and formulae etc:

SO11466034

Excel’s RGB() function actually creates a BGR value (I don’t think anybody that might know why is saying why though) so Excel shows nibbles in reverse order. For the code Columns3,4,5 was logical but BGR rather than the conventional RGB in the image I thought might look odd. For F in the image the C3 value (the LEFT hand column of the ‘RGB’ three) is derived from applying RIGHT() to the Hex colour.

More Related Contents:

  1. Duplicate number and it value in column EXCEL [closed]
  2. VBA – How to grab the cell below if a cell has text?
  3. Getting ScriptControl to work with Excel 2010 x64
  4. Pop up the Excel Statusbar?
  5. Get list of Excel files in a folder using VBA [duplicate]
  6. Conditional Formatting using Excel VBA code
  7. When one should use Set [e.g for SpecialCells return value]?
  8. Should I turn .CutCopyMode back on before exiting my sub procedure?
  9. Determine if cell contains data validation
  10. Sheet protection: UserInterFaceOnly gone
  11. VBScript – How to make program wait until process has finished?
  12. Removing special characters VBA Excel
  13. Open an Excel file from SharePoint site
  14. VBA recognizing workbook by partial name
  15. Excel VBA wildcard search
  16. How do I loop an excel 2010 table by using his name & column reference?
  17. Excel VBA Project has generated multiple Workbook objects
  18. Copy an entire worksheet to a new worksheet in Excel 2010
  19. How to error handle 1004 Error with WorksheetFunction.VLookup?
  20. How can Excel VBA open file using default application
  21. How to compare two entire rows in a sheet
  22. How to use workbook.saveas with automatic Overwrite
  23. Saving a Excel File into .txt format without quotes
  24. Fastest way to delete rows which cannot be grabbed with SpecialCells
  25. Combine multiple Excel workbooks into a single workbook
  26. Excel Macro, inserting internationally valid formula during run-time
  27. vba paste not working
  28. mac excel vba loop : from list & then export as pdf
  29. Excel VBA: auto click and open file from website
  30. How do I activate a specific workbook and a specific sheet?

Leave a Comment