How do I cause excel to skip number sequence using colour?

[Sampoline]

I have an example I would like to make a reality with a formula for Column C.

In Column B, I have used a formula to create a naming convention using Column A (check the formula bar in the picture above for reference).

Column C is what my desired result should be at the end. I have just typed the values without formula. But this is how I want them to be after formula.

Essentially I want Excel to skip the lines with red/yellow fill and continue the sequence only when cell A and B fill is blank or not yellow/red.

If you wanted more context on my problem. I asked a previous question on another thread, on how to make excel write the value "Y" in a helper column when the cell is filled yellow/red. Here is the link to that: How to correct the formula so the sequence is correct?
That question has since been solved. This is just the next step I wanted to reach after that problem was solved. Thanks.

 

[6StringJazzer]

There is no worksheet function that can tell you what fill color a cell has. To apply logic based on color, you must use VBA. Are these colors applied manually, or by using conditional formatting? If conditional formatting, then you can use the same logic used in the conditional formatting rule.

Here is an example of a VBA function that can be used in a formula to return TRUE if the cell is red:

Public Function RedFill(Cell As Range) As Boolean
   RedFill = Cell.Interior.Color = RGB(256, 0, 0)
End Function

 

[Sampoline]

There is no worksheet function that can tell you what fill color a cell has. To apply logic based on color, you must use VBA. Are these colors applied manually, or by using conditional formatting? If conditional formatting, then you can use the same logic used in the conditional formatting rule.

Here is an example of a VBA function that can be used in a formula to return TRUE if the cell is red:

Public Function RedFill(Cell As Range) As Boolean
   RedFill = Cell.Interior.Color = RGB(256, 0, 0)
End Function

The function I use to identify red and yellow filled cells is:

Function getColor(Rng As Range, ByVal ColorFormat As String) As Variant
    Dim ColorValue As Variant
    ColorValue = Cells(Rng.Row, Rng.Column).Interior.Color
    Select Case LCase(ColorFormat)
        Case "index"
            getColor = Rng.Interior.ColorIndex
        Case "rgb"
            getColor = (ColorValue Mod 256) & ", " & ((ColorValue \ 256) Mod 256) & ", " & (ColorValue \ 65536)
        Case Else
            getColor = "Only use 'Index' or 'RGB' as second argument!"
    End Select
End Function

The function works fine. My question is, how do I write an IF statement to skip cells (like shown in my OP above for Column C)

 

[Sampoline]

UPDATE: I've added two extra columns. Column C now is a helper column. I've used the below formula to give me a "Y" when the cell is red or yellow.

=IF(OR(3=GetColor(A2,"Index"),6=GetColor(A2,"index")),"Y","")

Then in Column D, I have the formula:

=IF(C2="Y","",A2&"_"&TEXT(COUNTIF($A$2:A2,A2),"0000")&"_"&"m")

Which skips when Y is found in Column C. But still not changing sequence number "_0000" as I wanted. Any solutions for this or better methods of tackling this, without a helper column would be beneficial..

 

[alansidman]

crossposted: How do I cause excel to skip number sequence using colour?

 

[Sampoline]

UPDATE: I've added two extra columns. Column C now is a helper column. I've used the below formula to give me a "Y" when the cell is red or yellow.

=IF(OR(3=GetColor(A2,"Index"),6=GetColor(A2,"index")),"Y","")

View attachment 27409

Then in Column D, I have the formula:

=IF(C2="Y","",A2&"_"&TEXT(COUNTIF($A$2:A2,A2),"0000")&"_"&"m")

Which skips when Y is found in Column C. But still not changing sequence number "_0000" as I wanted. Any solutions for this or better methods of tackling this, without a helper column would be beneficial..

This question has since been solved. Thanks anyway.