Is reverse Conditional Formatting possible?

[Scancon]

Hi, I want to mark a cell (e.g. A1) with a '1' if B1 is red and '0' if B1 has no color. Is this possible?
(I have a large sheet, with rows in different colors, and I need to sort the rows by color).
TIA! /Hans

 

[Lewiy]

It is possible to do this using VBA. Perhaps you can be more specific about which cells you want to change based on which other cell's colours.

 

[Scancon]

Sure!

I have the sheet where the rows are either red or not-colored.
I insert a new column as column A.
If cell B1 is red, I want to set the value of cell A1 to 1.
If cell B1 is not red, I want to set the value of cell A1 to 0.
Etc. through all the rows in the sheet.
(I have corrected a mistake in my original posting, sorry!).
/Hans.

 

[Lewiy]

This should do what you want (and it will insert a column for you as well so you don’t have to do this yourself)

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

 

[Scancon]

Hi Lewiy,

Thanks a lot - it works like a charm!

I just had a small problem, re. the setting of the variable 'limit'. If you pass a blank cell, the counting seems to stop, like when du do an 'end-down' to reach the end of data in a column. This was solved - and once again thank you for your kind assistance!

/Hans