Coloring column cells based on values

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
100
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).

grey color1
grey color1
no color2
no color2
no color2
grey color3
grey color3
no color4

<tbody>
</tbody>

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.

1grey filled
2no fill
2no fill
3grey filled
3grey filled
3grey filled
4no fill
5grey filled

<tbody>
</tbody>
Help appreciated!

Thanks
Deepk
 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
100
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,626
Office Version
365
Platform
Windows
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

Board Regular
Joined
Mar 21, 2018
Messages
100
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.
 

louisH

Board Regular
Joined
Mar 14, 2018
Messages
152
Hi,

Can you try this one for the first inquiry ?

Code:
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 ?
 
Last edited:

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
100
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

Board Regular
Joined
Mar 21, 2018
Messages
100
Hi,

Can you try this one for the first inquiry ?

Code:
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.
 
Last edited:

louisH

Board Regular
Joined
Mar 14, 2018
Messages
152
Try this one for the second inquiry :

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,020
Messages
5,466,085
Members
406,464
Latest member
buks1232000

This Week's Hot Topics

Top