+ Reply to Thread
Results 1 to 12 of 12

How to flag a cell a colour based on measurements entered elsewhere?

Hybrid View

  1. #1
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    How to flag a cell a colour based on measurements entered elsewhere?

    I have a spreadsheet which calculates cost of lots of different types of materials based on the size entered.

    there is a length cell (B5)
    and a width cell (B6)

    these are there for the user to enter the size into in mm.

    the table below that has lots of types of materials with rows of prices which change based on the size entered and each column represents what the material will be applied to.
    (I have attached a screenshot of it)

    for an example i will use row 13 which represents Vinyl 610mm
    there is column for media only, and the vinyl applied to various types of board 3mm/5mm/10mm etc, the formula in each cell calculates the cost based not he measurements and base costs worked out on a separate tab of the spreadsheet.

    how can i additionally tell specific cells to highlight a colour if the size entered is larger than the media to warn the user?
    For example cell B13 is the Vinyl, and shows the cost for the vinyl only at the size entered, but the maximum width for this is 610mm so i want it to highlight red to warn the user that the size entered is not possible.
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    Anyone able to help? if you need more info or a clearer explanation let me know, keen to try and get this sorted. cheers!

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,082

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    Post a sample file in excel format rather than an image. Images are impossible to edit and nobody wants to recreate the file for you.
    Attach a sample spreadsheet, remove any sensitive data.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,142

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    a spreadsheet sample rather than an image would be better
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    excel file attached let me know if my request needs any clarification
    Attached Files Attached Files

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,142

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    what happens the the template - its just the dimensions that re added and then the cost calculated

    so the cells
    B13,14,15
    are they the cells that are wrong in the example and would that text always be the same

    which means you need to extract the 610
    which is in a different text location
    and compare to the width , in a conditional formating

    OR you could just have a fixed validation of 610

    so in the width cell
    B6
    use conditional formatting

    =B6>610

    then turn red

  7. #7
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    Thanks for your response, yes the price calculated isn't necessarily wrong in those fields but those are narrower rolls of media, but id like to say for those cells if width is greater than 610 then turn red, and in the other if it is greater than 1220 then turn red etc, i just don't know how to set the conditional formatting to do that, if you could give me a quick explanation how to set that id appreciate it.

    I'm sure its simple but I'm not 100% sure where to start, its a feature I've not used before.

  8. #8
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    think i figured it out, went to conditional format> +rule> classic> use formula and added as you suggested =B6>610 then chose the highlight red option....

    this seemed to work, however i wanted to do this for multiple different cells instead od setting them individually, but if i try selecting multiple cells and setting this the same way is gets pretty messed up and confused

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,142

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    what cells are you trying to select ?

    the range you select
    say B6:G200
    the formula has to start at B6
    if you select A1:G200
    and then the formula is B6 > ....

    then the formula will look at A1 as B6
    and get it all wrong

    The formula has to be
    A1 > ...
    the same as the range starting point , if that makes sense

    you may need different rules for different ranges
    as
    1200 is greater than 610 - so will be true
    Last edited by etaf; 10-16-2015 at 01:48 PM.

  10. #10
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    Ok heres an example, i want to select B13 - B14 and apply the same formular that if B6>610 then chnage it to red,
    then i want to select E13:L16 as a block and apply 2 seperate rules to say if B5>2440 then turn red and if B6 >1220 then turn to red.

    can i do this together to save time or do i need to do each individually?

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,142

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    you can select blocks

    BUT for E13:L16

    why would they check B5 and B6 ?

    the range E13:L16
    you need the formula to be E13 - so the cell is tested correctly
    OR
    if its always B5 no matter what the other cells

    the you use $
    $B$5
    will always look at that cell only and not change based on the range

  12. #12
    Registered User
    Join Date
    10-16-2015
    Location
    Scotland
    MS-Off Ver
    Excel Mac 2011
    Posts
    9

    Re: How to flag a cell a colour based on measurements entered elsewhere?

    ok thanks the $ thing is exactly what i needed to know by the sound sof things,

    the reason i want it to check B5 and B6 for those cells is because they are to represent the media going on to sheets of plastic, so if the length and/or wedth is over a certain size then i want a warning to flag up.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Colour a cell background if 'result' is entered by macro
    By Tanyab in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2013, 05:56 AM
  2. [SOLVED] Creating a colour code that displays in a specific % cell based upon figures entered
    By 'MMSA_MAX in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2012, 08:10 AM
  3. Help changing colour of cell depending on date entered
    By dazlia in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-07-2011, 10:59 AM
  4. [SOLVED] Change Row Background Colour When Text Is Entered Into A Cell
    By AlexRoberts in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2010, 01:28 PM
  5. How do i change the colour of a cell when text is entered
    By Deanobey in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-30-2009, 02:59 PM
  6. Auto change colour of cell when text / letter entered into cell
    By Nik_AU in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2007, 07:36 AM
  7. Replies: 1
    Last Post: 02-15-2006, 12:52 PM
  8. Replies: 0
    Last Post: 02-15-2006, 12:25 PM

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