Sarah has attended:
Excel VBA Intro Intermediate course
Access Advanced course
Access Intermediate course
Formatting Cell colour of columns
This is my code I've got which works but needs altering.
Sub colorvalue()
Dim MyCell
For Each MyCell In Range("bacon").Cells
If IsNumeric(MyCell) And MyCell <> "" Then
Select Case MyCell
Case Is < 13
MyCell.Interior.ColorIndex = 10
Case Is < 26
MyCell.Interior.ColorIndex = 4
Case Is < 38
MyCell.Interior.ColorIndex = 43
Case Is < 51
MyCell.Interior.ColorIndex = 6
Case Is < 63
MyCell.Interior.ColorIndex = 44
Case Is < 76
MyCell.Interior.ColorIndex = 45
Case Is < 88
MyCell.Interior.ColorIndex = 46
Case Is < 101
MyCell.Interior.ColorIndex = 3
End Select
End If
Next MyCell
End Sub
Basically this macro has worked on one sheet where I've called the whole of column F "bacon".
The thing is I have another 54 spreadsheets all laid out exactly the same way and I want to run the macro on each sheet.
I can't call all column F's bacon as for each sheet it wants a different name.
so how do I write in the code that on the sheet that I've got active at that time, to run this macro on the whole of column F? Without having to name any data ranges.
Sarah
RE: Formatting Cell colour of columns
Hi Sarah
Thanks for your question
I suggest creating a range object and then setting that equal to column F in the active sheet
For example
Dim rngRange as range
set rnnRange = activesheet.columns("F")
For each MyCell in rngRange.cells
And then carry on as before.
If you have problems let me know and we can try something else
Regards
Stephen
