SOLVED

Does EXCEL can do the reverse process of "Conditional Formatting"?

Copper Contributor

For example, if the cell is color by green, then, type A, if color is brown, then, type "E"

2 Replies
best response confirmed by HansVogelaar (MVP)
Solution

@HAI-FENG 

Excel does not have a built-in feature to do the reverse process of conditional formatting.

However, you can achieve this using VBA (Visual Basic for Applications) code. 

For example, you can insert a new column and use VBA code to check the color of cells in another column and set the value of cells in the new column based on the color of cells in the other column.

 

Here is an example of VBA code that can do what you want.

This code inserts a new column as column A and checks the color of cells in column B. If the cell in column B is red (ColorIndex = 3), it sets the value of the corresponding cell in column A to 1. If the cell in column B is not red, it sets the value of the corresponding cell in column A to 0.

Sub SortColours()
    Dim limit As Long
    Dim c As Long
    Columns(1).Insert shift:=xlToLeft
    limit = Cells(Rows.Count, 2).End(xlUp).Row
    For c = 1 To limit
        If Cells(c, 2).Interior.ColorIndex = 3 Then
            Cells(c, 1) = 1
        Else: Cells(c, 1) = 0
        End If
    Next c
End Sub

You can adjust this code to fit your specific needs by changing the column numbers and ColorIndex values. 

 

I hope this helps! 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE 

 

Dear Niko,

 

Many thanks for your guidance.

 

Really appreciate it.

 

Best Regards,

Haifeng

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@HAI-FENG 

Excel does not have a built-in feature to do the reverse process of conditional formatting.

However, you can achieve this using VBA (Visual Basic for Applications) code. 

For example, you can insert a new column and use VBA code to check the color of cells in another column and set the value of cells in the new column based on the color of cells in the other column.

 

Here is an example of VBA code that can do what you want.

This code inserts a new column as column A and checks the color of cells in column B. If the cell in column B is red (ColorIndex = 3), it sets the value of the corresponding cell in column A to 1. If the cell in column B is not red, it sets the value of the corresponding cell in column A to 0.

Sub SortColours()
    Dim limit As Long
    Dim c As Long
    Columns(1).Insert shift:=xlToLeft
    limit = Cells(Rows.Count, 2).End(xlUp).Row
    For c = 1 To limit
        If Cells(c, 2).Interior.ColorIndex = 3 Then
            Cells(c, 1) = 1
        Else: Cells(c, 1) = 0
        End If
    Next c
End Sub

You can adjust this code to fit your specific needs by changing the column numbers and ColorIndex values. 

 

I hope this helps! 

NikolinoDE

I know I don't know anything (Socrates)

View solution in original post