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

arthurbr · Jan 15, 2018

And how are the cells colored? Manually or with a CF ?

naveeddil · Jan 15, 2018

It manually colored with RED, GREEN and YELLOW but as i have limited three colors so i can give RGB codes as well

Jaafar Tribak · Jan 15, 2018

Something like this may work ..

Place this code in the ThisWorkbook Module : (Code assumes target sheet is "Sheet1"- Change code const as required)

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
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()
    Dim oCell As Range
    If ActiveSheet Is Sheets(TARGET_SHEET) Then
        If ActiveWindow.RangeSelection.Column = 1 Then
            For Each oCell In ActiveWindow.RangeSelection.Cells
                With oCell.Interior
                    oCell.Offset(, 1) = Switch(.Color = vbRed, 1, .Color = vbBlue, 2, .Color = vbYellow, 3)
                End With
            Next
        End If
    End If
End Sub

Jaafar Tribak · Jan 15, 2018

A small code update in case the user manually chooses a color other than Red,Blue or Yellow :

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
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()
    Dim oCell As Range
    If ActiveSheet Is Sheets(TARGET_SHEET) Then
        If ActiveWindow.RangeSelection.Column = 1 Then
            For Each oCell In ActiveWindow.RangeSelection.Cells
                With oCell.Interior
                    oCell.Offset(, 1) = Switch(.Color = vbRed, 1, .Color = vbBlue, 2, .Color = vbYellow, 3, True, "")
                End With
            Next
        End If
    End If
End Sub

naveeddil · Jan 15, 2018

Dear Jafaar,

Thank you for helping me out and working to update code for me.

I have attached the sample data sheet and would request you to review. I want this sheet to have 1,2,3 after every cell (column L) having Red, Green and YELLOW colors.

I put the code but unable to make it work.

Can you please review.

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

Jaafar Tribak · Jan 16, 2018

Hi,

Place this code in the ThisWorkbook Module :

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
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()
    Dim oColorsRange As Range
    Dim oCell As Range
    If ActiveSheet Is Sheets(TARGET_SHEET) Then
        Set oColorsRange = Range(Columns(1).Cells(1), Columns(1).Cells(Rows.Count).End(xlUp)).Offset(, 10)
        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 If
End Sub

Note: The list you have at the moment contains 48 records but be aware that if the list grows considerably the code may start to slow down the worksheet due to the For Each Next loop taking more time.

Also, you have placed the code both in the workbook module and in the worksheet .. That's wrong. The code should go in the workbook module only so remove the one in the worksheet module.

Jaafar Tribak · Jan 16, 2018

Try this variation which is faster because it iterates through the whole list only when opening the workbook :

Code in the ThisWorkbook Module :

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(1), 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 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

arthurbr · Jan 16, 2018

naveeddil said:


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.

Could please tell us what want to do with the numbers you give to each colour?

naveeddil · Jan 17, 2018

arthurbr said:
Could please tell us what want to do with the numbers you give to each colour?

I will need to count it in pivot table for difference time and locations.

Opposite of Conditional Formatting

naveeddil

New Member

Excel Facts

arthurbr

Well-known Member

naveeddil

New Member

Jaafar Tribak

Well-known Member

Jaafar Tribak

Well-known Member

naveeddil

New Member

Jaafar Tribak

Well-known Member

Jaafar Tribak

Well-known Member

arthurbr

Well-known Member

naveeddil

New Member

Similar threads

Forum statistics

Share this page

Opposite of Conditional Formatting

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New 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