naveen200848
New Member
how to find the color code of a conditionally formatted cell. Later I would like to use this color code in if formula.
how to find the color code of a conditionally formatted cell. Later I would like to use this in if
- Thread starter naveen200848
- Start date
Naveen
Firstly, Welcome to the Chandoo.org Forums
As Chiro said it is very difficult to access the CF colors even using VB
If you want to use the colors to trigger something else, you are better off replicating the CF logic and using that in the new use.
If you attach a sample file we may be able to offer alternative solutions
Firstly, Welcome to the Chandoo.org Forums
As Chiro said it is very difficult to access the CF colors even using VB
If you want to use the colors to trigger something else, you are better off replicating the CF logic and using that in the new use.
If you attach a sample file we may be able to offer alternative solutions
bobhc
Excel Ninja
This may help. I am not the author
How to use:
Test the code:
Code:
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 SubHow to use:
- Copy the code above.
- Hit ALT + F11 to open the VBA Editor (VBE).
- Choose your workbook at left and hit Insert-Module from the menu.
- Paste the code into the code window that appears at right.
- Hit the Save and close the VBE.
Test the code:
- To test the function, apply Conditional Formatting to a cell, then enter a worksheet formula like:
- =ConditionalColor(A1,"interior") or =ConditionalColor(A1,"font")
- The first formula returns the color index number for the highlight color in cell A1, while the second returns the font color index number.
- 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.
- 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.
- 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
- ALT + F8 to open the Macro selector, select the NonConditionalFormatting macro and click the Run button
- Save the file with a different file name
- Note that all Conditional Formatting in the selected range will get wiped out, including bold or underline styles, borders and patterns.
- 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 cell In Selection"
Last edited by a moderator:
naveen200848
New Member
Hi bobhc.
Thank you for keeping your effort in putting the code and explaining it. But unexpectedly this code is not working and throwing some syntax errors as well.
I am not able to call the function itself to use in worksheet. I am not sure whether that function in code need somemore to get saved and can be called in sheet.
Please once have a look in it. You can save me.
Also attaching the sheet where I want to use this function
In Column L i highlighted some cells based on duplication from conditional formatting . Now in column K i have written vlookup for all cells of column L. Now I want the column L to perform vlookup based on if formula or vba code if the column K cells are highlighted in green.
Advance thanks
Thank you for keeping your effort in putting the code and explaining it. But unexpectedly this code is not working and throwing some syntax errors as well.
I am not able to call the function itself to use in worksheet. I am not sure whether that function in code need somemore to get saved and can be called in sheet.
Please once have a look in it. You can save me.
Also attaching the sheet where I want to use this function
In Column L i highlighted some cells based on duplication from conditional formatting . Now in column K i have written vlookup for all cells of column L. Now I want the column L to perform vlookup based on if formula or vba code if the column K cells are highlighted in green.
Advance thanks
naveen200848
New Member
Hi Hui,
Thanks for reply Attaching the sheet and what functionality I require.
In Column L i highlighted some cells based on duplication from conditional formatting . Now in column K i have written vlookup for all cells of column L. Now I want the column L to perform vlookup based on if formula or vba code if the column K cells are highlighted in green.
Advance thanks
Thanks for reply Attaching the sheet and what functionality I require.
In Column L i highlighted some cells based on duplication from conditional formatting . Now in column K i have written vlookup for all cells of column L. Now I want the column L to perform vlookup based on if formula or vba code if the column K cells are highlighted in green.
Advance thanks
Naveen
I'm confused
Above you mention that you want to perform a VLookup in Column L if the cell in Column K is green
But there is no CF or Green cells in Column K?
I am looking at the Error Log Format worksheet, is that correct?
Also Column L has an existing forumla =[@[Unique order code]]&N2&[@[Error Status]]
Can you please step through what your trying to achieve?
I'm confused
Above you mention that you want to perform a VLookup in Column L if the cell in Column K is green
But there is no CF or Green cells in Column K?
I am looking at the Error Log Format worksheet, is that correct?
Also Column L has an existing forumla =[@[Unique order code]]&N2&[@[Error Status]]
Can you please step through what your trying to achieve?
naveen200848
New Member
Sorry My bad Hui,
I was on out of internet for last 3 days sorry for late reply.
Its column L and Column M and column L with red highlights not green one. These red are duplicates and checked by conditional formatting.
You can see column m has some pre written vlookup formula and this needs to be worked if column L has red highlights
I was on out of internet for last 3 days sorry for late reply.
Its column L and Column M and column L with red highlights not green one. These red are duplicates and checked by conditional formatting.
You can see column m has some pre written vlookup formula and this needs to be worked if column L has red highlights