Opposite of Conditional Formatting

naveeddil · Jan 15, 2018

Dear All,

I am looking for some formula/ process in excel that is opposite of Conditional Formatting. I mean Conditional formatting is that If I have some value X in a cell so it colors the cell background with a certain color.

But I want exact opposite of it; I have color in some cells (hundreds in a column) and i want to use some formula to mark it as 1 for RED color, 2 for BLUE color and 3 for YELLOW in the very next cell of it.

Can you please guide.

Thank You

naveeddil · Jan 17, 2018

Dear Jaafar Tribak!

It works perfectly and fast now. the revised version make it much faster.
One thing as the condition is applied on whole column L (11) therefore the Title is also removed when i close and reopen the sheet thus the pivot table is not working as pivots do not accept blank heading.

Can you please please help.

https://drive.google.com/file/d/18xYTGIM98a6YKZaCcYHQaZfIc42w-ZRm/view?usp=sharing

Jaafar Tribak · Jan 17, 2018

Sorry, I missed excluding the header cell .

Try this :

Code:

Option Explicit

Private WithEvents cmbrs As CommandBars
Private Const TARGET_SHEET As String = "Sheet1" [B][COLOR=#008000]'<== Change sheet name as needed.[/COLOR][/B]

Private Sub Workbook_Open()
    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh Is Sheets(TARGET_SHEET) Then Set cmbrs = Application.CommandBars
End Sub

Private Sub cmbrs_OnUpdate()
    Static oColorsRange As Range
    Dim oCell As Range
    
    On Error Resume Next
    
    If oColorsRange Is Nothing Then
        Set oColorsRange = Sheets(TARGET_SHEET).Range(Columns(1).Cells(2), Sheets(TARGET_SHEET).Columns(1).Cells(Rows.Count).End(xlUp)).Offset(, 10)
        GoTo UpdateColors
    End If
    If ActiveSheet Is Sheets(TARGET_SHEET) Then
        If ActiveWindow.RangeSelection.Column = 11 And _
        Application.Intersect(ActiveWindow.RangeSelection, Range("k1")) Is Nothing Then
            Set oColorsRange = ActiveWindow.RangeSelection.Cells
            GoTo UpdateColors
        End If
    End If
    Exit Sub
UpdateColors:
        For Each oCell In oColorsRange
            With oCell.Interior
                oCell.Offset(, 1) = Switch(.Color = vbRed, 1, .Color = &H50B000, 2, .Color = vbYellow, 3, True, "")
            End With
        Next
End Sub

naveeddil · Jan 17, 2018

Thank You for awesome support and help!
You're great

Jaafar Tribak · Jan 17, 2018

naveeddil said:
Thank You for awesome support and help!
You're great

Happy to help and thanks for the feedback.

Opposite of Conditional Formatting

naveeddil

New Member

naveeddil

New Member

Excel Facts

Jaafar Tribak

Well-known Member

naveeddil

New Member

Jaafar Tribak

Well-known Member

Similar threads

Forum statistics

Share this page

Opposite of Conditional Formatting

naveeddil

New Member

naveeddil

New Member

Excel Facts

Jaafar Tribak

Well-known Member

naveeddil

New Member

Jaafar Tribak

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock