Results 1 to 5 of 5

Thread: Conditional Formatting - If/And Formula

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    Conditional Formatting - If/And Formula

    Hi there

    I have a spreadsheet that I need to conditional format based around certain qualifications.

    One of my columns is a 'Classification' column - being A, B, C, or T.

    Another of my columns is the number of visits we do to each row of data per calendar year.

    I need to highlight my number of visits column based on the classification, and the number of visits (as the number of visits changes with each classifcation.

    i.e. If someone is classified as "B", and the number of visits is greater than 12, then I want to code them blue. But is someone is an "A", and the number of visits is between 12 & 48, then they need to be green etc. etc

    So I wrote the following formula in the conditional formatting rule, and it didn't come up with an error, but it didn't seem to work either. Can anyone please please help??

    =IF($E:$E="A",(AND($AA:$AA>48)))

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Katrina,

    Welcome to ExcelFox !!

    Could you please attach a sample workbook? Feel free to remove any confidential data and put some dummy values.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Thanks, i've attached a sample now.Excel Fox.xlsx

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Select AA2:AA60,

    In CF, the formulas will be..

    =AND($E2="B",$AA2>12)

    =AND($E2="A",$AA2>12,$AA2<=48)

    Now I think you can write the formulas for other conditions.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Thank you so much!

Similar Threads

  1. Get Displayed Cell Color (whether from Conditional Formatting or not)
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 14
    Last Post: 10-21-2013, 07:11 PM
  2. Conditional formatting
    By mahmoud-lee in forum Excel Help
    Replies: 9
    Last Post: 05-30-2013, 03:00 PM
  3. Conditional Formatting using formula.
    By Ryan_Bernal in forum Excel Help
    Replies: 2
    Last Post: 02-18-2013, 11:33 PM
  4. Replies: 2
    Last Post: 09-16-2012, 02:28 AM
  5. Replies: 4
    Last Post: 07-17-2012, 12:49 AM

Posting Permissions

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