[ms office - 2003] First pesty macro in Excel. Trolling me hard.

[El_Nombre]

Hi helpful folks - I guess form reading the title you've stepped in to take pity on a poor noob so I'm already grateful to you for reading!

 

Well, never written a macro myself before only recorded the odd very basic procedure. I need to write a procedure where, based on a numerical value of 1-5 in a certain column, the background colour of that cell will change to one of five corresponding colours. Conditional formatting only allows for three colours so I have to do it this way.

 

I rummaged around for some code and found this page but I'm stuck as to what to do from there! How on earth do I, like, make it work? I'm so confused. I can about manage changing the colours and their related numbers but not assign the column it needs to apply to nor get it to run or save it in a situation where staff could us it on one or more spreadsheets and feeling as if I've bitten off more than I can chew here.

 

Can anyone steer me right or outright spoonfeed me this?

 

Thanks for reading all that.

[jamesreedersmith]

Easiest solution is go to office 2007/2010 the conditional formatting tools are much better.

[LosOjos]

Does it need to be automated (i.e. every time a value is entered in that column it's checked and highlighted) or could it be done by highlighting the relevant column and running a macro as and when you need to?

[El_Nombre]

Easiest solution is go to office 2007/2010 the conditional formatting tools are much better.

 

Haha yes it would be. Unfortunately that's not an option until the summer.

 

Does it need to be automated (i.e. every time a value is entered in that column it's checked and highlighted) or could it be done by highlighting the relevant column and running a macro as and when you need to?

 

Yes it definitely could be done that way!

[LosOjos]

In that case, create a module in VBA to store your procedures in, then add the following code to it:

 

Public Sub Apply_Colour()
Dim x As Range
For Each x In Selection
   Select Case x.Value
       Case 1:
           x.Interior.Color = vbRed
       Case 2:
           x.Interior.Color = vbYellow
       Case 3:
           x.Interior.Color = vbCyan
       Case 4:
           x.Interior.Color = vbMagenta
       Case 5:
           x.Interior.Color = vbBlue
       Case Else:
           x.Interior.Color = vbWhite
   End Select
Next x
End Sub

 

 

I think the code is pretty self explantory, just highlight the cells you want to apply it to and run the Macro (I'd add a button for it to your toolbar to speed things up if I were you!).

 

I've picked random colours from the limited range of constants in VBA, but you can use this function to find what the colour code is for a particular cell (use it in your spreadsheet as you would any other Excel forumla after you've added the code to your VBA module):

Public Function cell_colour(Cell_Check As Range) As Long
cell_colour = Cell_Check.Interior.Color
End Function

[JOrdan01070]

Have a look at the attached spreadsheet it should have the info you need

Master.xls

[El_Nombre]

Almost literally jumping for joy thank you both so much.

[JOrdan01070]

no problem gald it was what you needed