98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Formatting Cell colour of columns
Formatting Cell colour of columns
Resolved · Low Priority · Version 2003
Fri 9 Apr 2010, 16:41Delegate Sarah said...
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
Mon 12 Apr 2010, 09:17
Edited on Mon 12 Apr 2010, 09:17Trainer Stephen said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Wrapping Text in a Cell in an Excel 2010 WorkbookWhen you have a lot of text you want to put in a particular cell but you can't decrease the font size to fit because the text will then become ineligible, then manually wrap the text in a cell by simply pressing ALT+ENTER. |
