Clear cell contents based on colorSolved/Closed

Question

Hello,

I have a range of cells (E7:AI18) which I want to delete the contents of, if the color is white, gray-25% or gray-80%.

So if cell E7 has the color white, gray-25% or gray-80%, clear content. Any other color, keep contents.
Do this for every cell in range E7:AI18.

Thanks in advance for the help.

Best regards,
Trowa

Trowa · Accepted Answer

Hi Kaiyasit,

Thanks alot, this is exactly what I was looking for.

Still don't understand how that "Dim as" stuff works, but it's good to know I can come to this site for these kind of codes.

Best regards,
Trowa

kaiyasit · Answer

Try this.........
Sub Testing()
        Dim iColor As Long
        Dim i, j As Integer
        
        For I = 7 To 18
                For j = 5 To 35
                        iColor = Cells(i, j).Interior.Color
                        
                        Select Case iColor
                                Case 16777215 ' white color
                                         Cells(i, j).ClearContents
                                Case 12632256 ' gray - 25%
                                        Cells(i, j).ClearContents
                                Case 3355443 'gray - 80%
                                        Cells(i, j).ClearContents
                        End Select
                Next j
        Next i

'E7:AI18
'white, gray-25% or gray-80%
End Sub

kaiyasit · Answer

This is VBA code that mean you must do on below

1. Swith to VBA editor mode by press Atl + F11
2. copy this code to VBA editor
3. swith to Excel sheet by press Atl + F11
4.on toolbar, Click right and select "Forms" menu
5.click button and creat it to your sheet
6.select procedure name and click OK.

If you still don't understand please sent your file to me.
I will make the example on your sheet and sent back to you..........

kaiyasitp@gmail.com

Best regards,
Kaiyasit Phanmakorn

Trowa · Answer

Hi Kaiyasit,

Apparently you misunderstood me. I know how to apply the code. I'm just not able to create the code myself.

So thanks again for the code, it works perfect.

Maybe you can tell me how you got the color codes? I'm used to create a macro, applying a color to a cell and then read the code in VBA. The result is then a one or two digit number, example: gray-25% = 15. So how do you get 12632256 for gray-25%?

Thanks for the time you are putting into this.

Best regards,
Trowa

Trowa · Answer

Hi Kaiyasit,

Sorry for the double post, but I wanted to ask you another thing.

This code works for range E7:AI18. I want to add the following ranges:
E35:AI46, E54:AI65, E73:AI84, E92:AI103.

Now I have created 5 modules, but I would like to put it in one single module as one code.

How would I do that?

Many thanks,
Trowa

kaiyasit · Answer

'get corlor number use below code
Sub FindInteriorColorNumber()
        'Chage color at A1 and run this code
        MsgBox Range("A1").Interior.Color
End Sub

Code on below is combine your module
'===============================================
Sub UPDATEDATA()
'This code works for range E7:AI18. I want to add the following ranges:
'E35:AI46, E54:AI65, E73:AI84, E92:AI103.

'Testing(from_row,to_row,from_Column,to_Column)
'range E7:AI18
        Call Testing(7, 18, 5, 35)
'Range E35:AI46
        Call Testing(35, 46, 5, 35)
'range E54:AI65
        Call Testing(54, 65, 5, 35)
'range E73:AI84
        Call Testing(73, 84, 5, 35)
'range E92:AI103
        Call Testing(92, 103, 5, 35)
End Sub

Sub Testing(ByVal FR_ As Integer, ByVal TR_ As Integer, ByVal FC_ As Integer, ByVal TC_ As Integer)
        Dim iColor As Long
        Dim i, j As Integer
        For I = FR_ To TR_
                For j = FC_ To TC_
                   iColor = Cells(i, j).Interior.Color
                        Select Case iColor
                                Case 16777215 ' white color
                                         Cells(i, j).ClearContents
                                Case 12632256 ' gray - 25%
                                        Cells(i, j).ClearContents
                                Case 3355443 'gray - 80%
                                        Cells(i, j).ClearContents
                        End Select
                Next j
        Next i

'E7:AI18
'white, gray-25% or gray-80%
End Sub

Best regards,
Kaiyasit Phanmakorn

Trowa · Answer

Thanks for your answers Kaiyasit.

You helped me out alot.

Best regards,
Trowa

Clear cell contents based on color

7 replies

Subscribe To Our Newsletter!