+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting

  1. #1
    Registered User
    Join Date
    02-05-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    59

    Conditional formatting

    Hello,

    I have attached an Excel file containing data. I want to select cells B2 to G164 and do two things:-

    a) identify all cells of 66 and over (or any number I choose to select), and,

    b) colour the selected cells with a selectable colour.

    The data consists of temperature readings in a solar hot water heater box.

    At the age of 65 "I am getting there." I have been to the local library and sourced Excel books for study purposes, but I always find it difficult to identify the right formula for a purpose. The maths is not so difficult.

    Thank you for your assistance.

    David in Hong Kong
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,945

    Re: Conditional formatting

    Put 66 into a cell by itself (I used A1).
    Then select Format/Conditional Formatting
    change the 2nd dropdown to "greater than or equal to"
    in the 3rd box enter =$A$1 (or whichever cell you decide to use)
    format/patterns to the color you want

    for b), I think you are out of luck if you want to have a "selectable color" You can set this manually in CF, but you cant do it automatically
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-05-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Conditional formatting

    Ah, Ha. Thank you for your note.

    I think I must do two things:- upgrade to Office 2010 and study Visual Basic.

    David in Hong Kong

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Conditional formatting

    For (b), you could create a userform that contans a grid of command buttons.
    Set the back color of each button to the color you want for the selected cells. Code the command button to color the selected cells

    Basic example:

    When the user form is initialized, set the back color of all command button

    Please Login or Register  to view this content.
    For each button in the userform, code it like so:
    Please Login or Register  to view this content.
    You can determine the correct RGB color value by simply opening up the format cells dialog (Ctrl + 1 keys).
    Fill Tab > More Colors > Custom
    Move the color selector (in the large color grid) to the color range then move the slide (narrow strip to the right) to the desired color.
    Note the RGB values for the selected color. These are the values to use in the code.

    See attached example.

    Excel-Help_Color_Cells.xls
    Last edited by Palmetto; 03-09-2013 at 09:04 AM.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  5. #5
    Registered User
    Join Date
    02-05-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Conditional formatting

    Palmetto,

    Thank you for taking the time to sit down and produce the suggestion given above. I am most grateful.

    The concept is so simple so I shall play around with it later today. I am learning the wonders of Excel quite quickly.

    David in Hong Kong

+ 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