Menu
Ask a question Report

Clear cell contents based on color [Solved/Closed]

Trowa - Aug 6, 2009 at 08:00 AM - Latest reply:  Trowa
- Aug 17, 2009 at 08:09 AM
Office software Ask a question Report
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
See more 

7 replies

Best answer
Report Respond to Trowa
Trowa - Aug 10, 2009 at 07:10 AM
6
Thank you
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

Thank you, Trowa 6

Something to say? Add comment

CCM has helped 1671 users this month

Report Respond to Trowa
Trowa - Aug 13, 2009 at 07:31 AM
2
Thank you
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
Report Respond to kaiyasit
kaiyasit 30 Posts Sunday August 9, 2009Registration date April 20, 2010 Last seen - Aug 10, 2009 at 04:31 AM
1
Thank you
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
Report Respond to Trowa
Trowa - Aug 13, 2009 at 08:08 AM
1
Thank you
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
Report Respond to Trowa
Trowa - Aug 17, 2009 at 08:09 AM
1
Thank you
Thanks for your answers Kaiyasit.

You helped me out alot.

Best regards,
Trowa
Report Respond to kaiyasit
kaiyasit 30 Posts Sunday August 9, 2009Registration date April 20, 2010 Last seen - Aug 12, 2009 at 05:54 AM
0
Thank you
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
Report Respond to kaiyasit
kaiyasit 30 Posts Sunday August 9, 2009Registration date April 20, 2010 Last seen - Aug 14, 2009 at 01:11 AM
0
Thank you
'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
Ask a question
Most popular