Excel

Get Cell Color Function

Ease of Use

Easy

Version tested with

97, 2000, 2003 

Submitted by:

byundt

Description:

Function returns the active cell interior or font color index, regardless of whether it was set by regular or Conditional Formatting. 

Discussion:

People sometimes want to know what color a cell is. The answer is complicated if conditional formatting is used because you must first evaluate the conditional formatting criteria to see if any of them govern cell color. For users of Excel 95 and other spreadsheet applications that don't support conditional formatting, you can use this information to convert Conditional Formatting to regular formatting with the same colors. 

Code:

instructions for use

			

Function ConditionalColor(rg As Range, FormatType As String) As Long 'Returns the color index (either font or interior) of the first cell in range rg. If no _ conditional format conditions apply, Then returns the regular color of the cell. _ FormatType Is either "Font" Or "Interior" Dim cel As Range Dim tmp As Variant Dim boo As Boolean Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String Dim i As Long 'Application.Volatile 'This statement required if Conditional Formatting for rg is determined by the _ value of other cells Set cel = rg.Cells(1, 1) Select Case Left(LCase(FormatType), 1) Case "f" 'Font color ConditionalColor = cel.Font.ColorIndex Case Else 'Interior or highlight color ConditionalColor = cel.Interior.ColorIndex End Select If cel.FormatConditions.Count > 0 Then 'On Error Resume Next With cel.FormatConditions For i = 1 To .Count 'Loop through the three possible format conditions for each cell frmla = .Item(i).Formula1 If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True 'Conditional Formatting is interpreted relative to the active cell. _ This cause the wrong results If the formula isn 't restated relative to the cell containing the _ Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _ If the Function were Not called using a worksheet formula, you could just activate the cell instead. frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell) frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel) boo = Application.Evaluate(frmlaA1) Else 'If "Value Is", then identify the type of comparison operator and build comparison formula Select Case .Item(i).Operator Case xlEqual ' = x frmla = cel & "=" & .Item(i).Formula1 Case xlNotEqual ' <> x frmla = cel & "<>" & .Item(i).Formula1 Case xlBetween 'x <= cel <= y frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")" Case xlNotBetween 'x > cel or cel > y frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")" Case xlLess ' < x frmla = cel & "<" & .Item(i).Formula1 Case xlLessEqual ' <= x frmla = cel & "<=" & .Item(i).Formula1 Case xlGreater ' > x frmla = cel & ">" & .Item(i).Formula1 Case xlGreaterEqual ' >= x frmla = cel & ">=" & .Item(i).Formula1 End Select boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula End If If boo Then 'If this Format Condition is satisfied On Error Resume Next Select Case Left(LCase(FormatType), 1) Case "f" 'Font color tmp = .Item(i).Font.ColorIndex Case Else 'Interior or highlight color tmp = .Item(i).Interior.ColorIndex End Select If Err = 0 Then ConditionalColor = tmp Err.Clear On Error Goto 0 Exit For 'Since Format Condition is satisfied, exit the inner loop End If Next i End With End If End Function Sub NonConditionalFormatting() Dim cel As Range Application.ScreenUpdating = False 'Remove conditional formatting from entire worksheet 'For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions) For Each cel In Selection 'Remove conditional formatting from selected cells If cel.FormatConditions.Count > 0 Then cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell End If Next cel Application.ScreenUpdating = True End Sub

How to use:

  1. Copy the code above.
  2. Hit ALT + F11 to open the VBA Editor (VBE).
  3. Choose your workbook at left and hit Insert-Module from the menu.
  4. Paste the code into the code window that appears at right.
  5. Hit the Save diskette and close the VBE.
 

Test the code:

  1. To test the function, apply Conditional Formatting to a cell, then enter a worksheet formula like:
  2. =ConditionalColor(A1,"interior") or =ConditionalColor(A1,"font")
  3. The first formula returns the color index number for the highlight color in cell A1, while the second returns the font color index number.
  4. Note that the function will update only when cell A1 changes its value--which may not coincide with color changes if the Conditional Formatting is based on the value of another cell. If so, uncomment the Application.Volatile statement in the function.
  5. Note too, that the function returns incorrect results if the Conditional Formatting is based on a formula and the sheet being interrogated is not the active sheet. To get around this difficulty, activate the sheet before calling the function.
  6. The NonConditionalFormatting sub wipes out all Conditional Formatting from the selected cells, replacing it with regular formatting using the same colors. To use the sub, select the a range of cells that need their Conditional Formatting converted to regular formatting
  7. ALT + F8 to open the Macro selector, select the NonConditionalFormatting macro and click the Run button
  8. Save the file with a different file name
  9. Note that all Conditional Formatting in the selected range will get wiped out, including bold or underline styles, borders and patterns.
  10. Note too, if you want all the Conditional Formatted cells on the worksheet changed at once, there is a line in the macro that has been commented out for this purpose. Delete the apostrophe in front of this line and add one in front of the line beginning "For Each cel In Selection"
 

Sample File:

ConditionalFormattingToRegularFormatting.zip 14.01KB 

Approved by mdmackillop


This entry has been viewed 325 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express