+ Reply to Thread
Results 1 to 5 of 5

turning ranges red, amber or green

  1. #1
    Forum Contributor
    Join Date
    11-22-2006
    MS-Off Ver
    2010
    Posts
    201

    turning ranges red, amber or green

    Hi, i have a spreadsheet with a number of named ranges called

    section1red, section1amber and section1green
    section2red, section2amber and section2green
    section3red, section3amber and section3green

    Cells in each range may or not be populated with integers (always positive).

    Is there a way using VBA to automatically turn the range:
    section1red to red background
    section1amber to amber background
    section1green to green background

    when the cell is populated with a value and ignore otherwise (ie: remain white) ?

    Hope that is achieveable, thanks for your help.
    Luce x

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    How about conditional formatting?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    11-22-2006
    MS-Off Ver
    2010
    Posts
    201
    Thought about that, but i cant see how you can define a range in conditional formatting. thanks

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372
    Do the ranges consist of more than one cell or is each range a single cell?

    If single cell, just select the cell and set conditional formatting if the cell value is greater than zero.

    Same principle applies if the range consists of more than one cell

    The fact that a cell has a range name does not affect how it can be formatted with conditional formatting.

    cheers

  5. #5
    Forum Contributor
    Join Date
    11-22-2006
    MS-Off Ver
    2010
    Posts
    201
    Hi, got it working using this vba, thanks.

    Sub formatting()
    Cells.Select
    Selection.Interior.ColorIndex = 2

    Range("section1green").Select
    Selection.Interior.ColorIndex = 4

    Range("section1amber").Select
    Selection.Interior.ColorIndex = 44

    Range("section1red").Select
    Selection.Interior.ColorIndex = 3

    End

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1