Office Software Thread, First pesty macro in Excel. Trolling me hard. in Technical; Hi helpful folks - I guess form reading the title you've stepped in to take pity on a poor noob ...
28th January 2011, 12:32 PM #1
First pesty macro in Excel. Trolling me hard.
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.
IDG Tech News
28th January 2011, 12:33 PM #2
Easiest solution is go to office 2007/2010 the conditional formatting tools are much better.
28th January 2011, 12:37 PM #3
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?
28th January 2011, 12:45 PM #4
Haha yes it would be. Unfortunately that's not an option until the summer.
Originally Posted by jamesreedersmith
Yes it definitely could be done that way!
Originally Posted by LosOjos
28th January 2011, 01:02 PM #5
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
x.Interior.Color = vbRed
x.Interior.Color = vbYellow
x.Interior.Color = vbCyan
x.Interior.Color = vbMagenta
x.Interior.Color = vbBlue
x.Interior.Color = vbWhite
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
Thanks to LosOjos from:
El_Nombre (28th January 2011)
28th January 2011, 01:45 PM #6
Have a look at the attached spreadsheet it should have the info you need
Thanks to JOrdan01070 from:
El_Nombre (28th January 2011)
28th January 2011, 02:05 PM #7
Almost literally jumping for joy thank you both so much.
28th January 2011, 02:13 PM #8
no problem gald it was what you needed
By Duall in forum Office Software
Last Post: 20th August 2010, 05:51 PM
By SteveB in forum MIS Systems
Last Post: 3rd March 2010, 10:23 AM
By ahuxham in forum Office Software
Last Post: 18th December 2009, 11:27 AM
By oyoung in forum Windows
Last Post: 30th March 2007, 11:27 AM
By steve in forum Educational Software
Last Post: 16th May 2006, 03:49 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)