Related:
- Clear cell contents based on color
- Excel clear cell contents if another cell changes - Best answers
- Change cell value based on color ✓ - Forum - Office Software
- Excel formula to clear cell contents - How-To - Excel
- Excel cell contents not visible in formula bar - How-To - Excel
- To get cell contents into Excel file property ✓ - Forum - Excel
- Copy text from cell below based on condition ✓ - Forum - Office Software
7 replies
Trowa
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
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
Trowa
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
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
kaiyasit
- Posts
- 30
- Registration date
- Sunday August 9, 2009
- Status
- Member
- Last seen
- April 20, 2010
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
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
Trowa
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
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
- Posts
- 30
- Registration date
- Sunday August 9, 2009
- Status
- Member
- Last seen
- April 20, 2010
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
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
kaiyasit
- Posts
- 30
- Registration date
- Sunday August 9, 2009
- Status
- Member
- Last seen
- April 20, 2010
'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
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
Subscribe To Our Newsletter!
