+ Post New Thread
Results 1 to 8 of 8
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 ...
  1. #1
    El_Nombre's Avatar
    Join Date
    Sep 2008
    Posts
    288
    Thank Post
    63
    Thanked 25 Times in 19 Posts
    Rep Power
    18

    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.

  2. #2
    jamesreedersmith's Avatar
    Join Date
    Sep 2009
    Location
    Ruskington
    Posts
    1,166
    Thank Post
    78
    Thanked 258 Times in 230 Posts
    Rep Power
    78
    Easiest solution is go to office 2007/2010 the conditional formatting tools are much better.

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,487
    Thank Post
    1,445
    Thanked 1,182 Times in 809 Posts
    Rep Power
    709
    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?

  4. #4
    El_Nombre's Avatar
    Join Date
    Sep 2008
    Posts
    288
    Thank Post
    63
    Thanked 25 Times in 19 Posts
    Rep Power
    18
    Quote Originally Posted by jamesreedersmith View Post
    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.

    Quote Originally Posted by LosOjos View Post
    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!

  5. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,487
    Thank Post
    1,445
    Thanked 1,182 Times in 809 Posts
    Rep Power
    709
    In that case, create a module in VBA to store your procedures in, then add the following code to it:

    Code:
    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):
    Code:
    Public Function cell_colour(Cell_Check As Range) As Long
    cell_colour = Cell_Check.Interior.Color
    End Function

  6. Thanks to LosOjos from:

    El_Nombre (28th January 2011)

  7. #6

    Join Date
    May 2007
    Location
    Birmingham
    Posts
    169
    Thank Post
    9
    Thanked 45 Times in 36 Posts
    Rep Power
    27
    Have a look at the attached spreadsheet it should have the info you need
    Attached Files Attached Files

  8. Thanks to JOrdan01070 from:

    El_Nombre (28th January 2011)

  9. #7
    El_Nombre's Avatar
    Join Date
    Sep 2008
    Posts
    288
    Thank Post
    63
    Thanked 25 Times in 19 Posts
    Rep Power
    18
    Almost literally jumping for joy thank you both so much.

  10. #8

    Join Date
    May 2007
    Location
    Birmingham
    Posts
    169
    Thank Post
    9
    Thanked 45 Times in 36 Posts
    Rep Power
    27
    no problem gald it was what you needed

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2007] Excel Macro - Save values individually
    By Duall in forum Office Software
    Replies: 1
    Last Post: 20th August 2010, 04:51 PM
  2. Macro + Excel 2007 + SIMS
    By SteveB in forum MIS Systems
    Replies: 20
    Last Post: 3rd March 2010, 09:23 AM
  3. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 10:27 AM
  4. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 10:27 AM
  5. Excel Macro - Edexcel GCE Exams
    By steve in forum Educational Software
    Replies: 0
    Last Post: 16th May 2006, 02:49 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •