+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting - What am I doing wrong?

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    4

    Conditional Formatting - What am I doing wrong?

    Maybe my question should be, Is conditional formatting the right way to go?

    I've just scratched the surface of Conditional Formatting within Excel. It seems pretty straight forward and all, but I can't figure out why it won't color a row when the value in the cell changes.

    We have a column that specifies the quarter of the year our job was shipped on. No problem, I was able to get Excel to change this column to Q1, Q2... based on our invoice date. Now is where it gets confusing for me

    So, my yearly quarters are in column A, and will be filled in with the date we invoice our customer. I would like all of row 26 to change to green when the quarter is Q1. I would like all of row 26 to change to yellow when the quarter is Q2. So on and so fourth.

    Now, I am by no means an Excel Pro, but this does seem like it's pretty easy to obtain. When I highlight the row I would like changed, select Conditional Formatting>New Rule. I select the "Use a formula to determine which cells to format". In the formula bar, I place =A26=Q1, then I select the formatting I would like, OK out of the dialog box and back to my sheet. I also do the same thing for creating a rule for Q2. If Q1 shows up in Column A, the row should go green, which it does, I'm happy. But as soon as I place a date that's in Q2 of the year, no formatting changes take place, and everything is still green as in the rule for Q1. When the value of the first column changes, no formatting takes place and everything stays as it was with the original value. Also, after the rule changes the background color of the row, once I place data in the cell, the color goes back to white.

    Do I have any steps out of order? Or am I going about this all wrong?

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Conditional Formatting - What am I doing wrong?

    Q1 is a cell reference, I'm guessing you are talking about changing a value in a cell somewhere to Q1?
    So your format condition is evaluating if the value in Q1 is equal to the value in A26 or something, I think that you want to select the row with A26 active and enter:
    =$A$26="Q1"
    as your conditional format.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: Conditional Formatting - What am I doing wrong?

    which order do you put the dates in - you could re-order the rules and then stop if true
    or use an AND statement

    also put a $ infront of the column $A26=Q1 and then apply to the row , will stop conditional formatting moving to B26, c26 and testing etc

    Q1 as its text should be in ""

    so $A26="Q1"

    So, because you have the Q1 test in conditional formating first , it executes in the order it finds the formulas and so thats true and if stop if true is ticked - thats the end of it

    so make sure stop if true is not ticked
    or put the Q2 test first and stop if true ticked

    post and example of your spreadsheet with no personal data - this is a public forum
    and we can see whats happening and advise
    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.

  4. #4
    Registered User
    Join Date
    10-28-2013
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Formatting - What am I doing wrong?

    I didn't realize I needed the quotation marks in there. You are both correct and I thank you both for taking the time to reply and help. Everything now works as it should...

  5. #5
    Registered User
    Join Date
    10-28-2013
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Formatting - What am I doing wrong?

    I spoke too soon. While the answers you've given me are correct, when I drag the cells down, it will copy the formatting, but it still looks at the original cell (Invoice Date) to look at the formatting for the new row. How do I specify the formatting to look at the new cell in the row I've created? I've attached the book for you to look at....
    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.83 (24031120))
    Posts
    8,739

    Re: Conditional Formatting - What am I doing wrong?

    try this
    i have applied it to the rows down to row 100
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-28-2013
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Formatting - What am I doing wrong?

    Thank you so much for helping me out, again! I was going mad. Looks like I'll have to brush up the Excel skills. Have a good holiday!

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: Conditional Formatting - What am I doing wrong?

    holiday ?

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

    Re: Conditional Formatting - What am I doing wrong?

    Oh yes, thanksgiven in US

    have a good holiday too

+ 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. [SOLVED] Conditional Formatting is Wrong Color
    By LSC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2013, 07:52 PM
  2. Highlight a cell with the wrong data without Conditional Formatting
    By k9mikep in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2010, 01:31 PM
  3. Conditional Formatting Code - Deletes Wrong Conditional Format
    By RSpecianJr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2010, 10:53 AM
  4. What is wrong with this conditional?
    By bvbaby in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-29-2009, 11:14 PM
  5. Conditional Formatting Help-ome light on what I'm doing wrong
    By amsnss in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-03-2007, 10:38 AM

Tags for this Thread

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