Menu
  1. Accueil
  2. Tech Forum
  3. Office Software

Clear cell contents based on color [Solved/Closed]

Report
-
 Trowa -
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
Related:

7 replies

Answer 1 / 7

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
6
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 7

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
Answer 3 / 7
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 11
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
Answer 4 / 7

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
Answer 5 / 7

Thanks for your answers Kaiyasit.

You helped me out alot.

Best regards,
Trowa
Answer 6 / 7
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 11
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
Answer 7 / 7
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 11
'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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!