Excel UDF Get the colour of the cell

Dealing with colours jn Excel became a lot more important when Office 2010 was released. Conditional formatting allowed for large ranges of slightly different shades of the same colour to be used in ranges which are not so easy to identify by sight.

This UDF example returns the value of the colour in the cell. There are literally 16 million different colours on the Excel palette so whilst we can’t list them all here, we have done so in another blog for you to use as a reference once you get your cell colour result.

This straightforward UDF must be copied and pasted into a VBA Module for it to become a ‘standard’ formula in your current workbook.

To use it, simply choose any cell and type the formula, including the () at the end:

=GetCellColour(A1)

This will return the cells filled colour by reference number (ie Yellow is 65535)

Feel free to send us a question or leave a comment below and we’ll answer your query in the open !

Function GetCellColour(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColour = arResults
Else
GetCellColour = xlRange.Interior.Color
End If
End Function


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

One thought on “Excel UDF Get the colour of the cell

  1. Pingback: Colour alternative rows in a worksheet | Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *