Coloring column cells based on values

[Deepk]

Hi team,

I need a macro that fill or not fill color (grey) to the cells of two adjacent columns based on change in values of the second column. Please see the example below. Here both cells in a row are either filled or not filled(including the numbered one).

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]grey color[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]grey color[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]no color[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]no color[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]no color[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]grey color[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]grey color[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]no color[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Also, I need second macro that work opposite to the above macro. Means, based on the color filled or not filled it assigns number in the previous column. see example below.

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]grey filled[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]no fill[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]no fill[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]grey filled[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]grey filled[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]grey filled[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]no fill[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]grey filled[/TD]
[/TR]
</tbody>[/TABLE]
Help appreciated!

Thanks
Deepk

 

[Gerald Higgins]

Why don't you just use Conditional Formating for the first bit ?

 

[Deepk]

Why don't you just use Conditional Formating for the first bit ?

Hi Gerald,

Thank you for your reply.

This is of my repeated use. conditional formatting is not enough for me. Please review my need once again and kindly provide me the code in possible.

Looking forward in anticipation.

Regards.

 

[Peter_SSs]

conditional formatting is not enough for me.

1. Can you explain what you mean by "not enough" and why conditional formatting is no good for you?

2. Is the second question at all related to the first? If so, can you explain how?

3. Is there, or could there be, a header row above the sample data you provided?

 

[Deepk]

1. Can you explain what you mean by "not enough" and why conditional formatting is no good for you?

2. Is the second question at all related to the first? If so, can you explain how?

3. Is there, or could there be, a header row above the sample data you provided?

Hi Peter,

Thank you for your swift response. Please find below the sample data. I have a long list.

ColumnA|Column B

Unique ID |Unique member
1A KP3767248
1A KP3767249
1A KP3767250
1A KP3767251
2B KP3767252
2B KP3767253
2B KP3767254
3A KP3767257
3A KP3767258
4W KP3767262
4W KP3767263
4W KP3767264
5F KP3767260
5F KP3767261
6Z KP3767262
6Z KP3767263
6Z KP3767264
7Q KP3767265

To be very frank, I am not sure how to use conditional formatting for such kind of data . I use conditional formatting for the data which are very straight forward. I think a macro would suffice the need.

Let me know in case you have further any question. Thank you.

 

[Peter_SSs]

Let me know in case you have further any question.

Yes ..

2. Is the second question at all related to the first? If so, can you explain how?

 

[louisH]

Hi,

Can you try this one for the first inquiry ?

Sub FillGreyColor()
    
    firstVal = 0
    modulo = 0
    
    Range("A:A").Interior.Color = xlNone
    
    For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        
        If c.Value <> firstVal Then
            modulo = modulo + 1
            firstVal = c.Value
        End If
        
        If modulo Mod 2 = 1 Then
            c.Interior.Color = 12566463
        End If
        
    Next c
End Sub

For the second question, what is the logic to populate with 1A, 2B, 3D, ... etc ?

 

[Deepk]

Yes ..

No, both are independent to each other. To be more precise on second question, based on the color from column B cells, the corresponding column A cells is numbered. See the table in the second question.

Thank you.

 

[Deepk]

Hi,

Can you try this one for the first inquiry ?

Sub FillGreyColor()
    
    firstVal = 0
    modulo = 0
    
    Range("A:A").Interior.Color = xlNone
    
    For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        
        If c.Value <> firstVal Then
            modulo = modulo + 1
            firstVal = c.Value
        End If
        
        If modulo Mod 2 = 1 Then
            c.Interior.Color = 12566463
        End If
        
    Next c
End Sub

For the second question, what is the logic to populate with 1A, 2B, 3D, ... etc ?

Please populate with number 1, 2 ,3 .... only. This will be my final output.

 

[louisH]

Try this one for the second inquiry :

Sub FillNumbers()
    firstVal = 0
    cellColor = 0
    
    For Each c In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        
        If c.Interior.Color <> cellColor Then
            firstVal = firstVal + 1
            cellColor = c.Interior.Color
        End If
        Cells(c.Row, 1).Value = firstVal
        
    Next c
End Sub