Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Conditional Formatting Help

Latest post Wed, Jul 29 2015 3:22 PM by coleburdette. 9 replies.
  • Wed, Jul 29 2015 11:54 AM

    • Hannah
    • Top 75 Contributor
    • Joined on Sun, Feb 7 2010
    • Inglis
    • Posts 12
    • Points 188

    Conditional Formatting Help

    Hi there, I need some help doing conditional formatting. We are developing a spreadsheet for clients to fill out and need spreadsheet to create red cells when a certain cell contains a value. I've attached and example of the spreadsheet. If someone says 1 day or 2 day in column C we need G5 to J5 to be hightlighted in red if it's blank. when a value is entered in, the colour is cleared. Can anyone help? I thought I had the formula, but I would have to create it individually for each cell and it wasn't quite working. Thanks heaps! Hannah
    • Post Points: 21
  • Wed, Jul 29 2015 12:41 PM In reply to

    Re: Conditional Formatting Help

    This should work for you. Make sure the absolute references are the same and that they are in the same order. And the color of the format on the first format needs to be chosen as white or "No Color". I don't think the picture is working. here are the formulas for the conditional formatting, Formula: =G1<>"" Format: No Color Fill Applies to: =$G:$J Formula: =$C1=1 Format: Red Fill Applies to: =$G:$J Formula: =$C1=2 Format: Red Fill Applies to: =$G:$J
    • Post Points: 21
  • Wed, Jul 29 2015 12:46 PM In reply to

    • Hannah
    • Top 75 Contributor
    • Joined on Sun, Feb 7 2010
    • Inglis
    • Posts 12
    • Points 188

    Re: Conditional Formatting Help

    Hi Coleburdette, I can't open the picture sorry. Just comes up with an error. Thanks, Hannah
    • Post Points: 21
  • Wed, Jul 29 2015 12:49 PM In reply to

    Re: Conditional Formatting Help

    I noticed that and edited my original reply but in case it doesn't notify you here it is And the color of the format on the first format needs to be chosen as white or "No Color". I don't think the picture is working. here are the formulas for the conditional formatting, Formula: =G1<>"" Format: No Color Fill Applies to: =$G:$J Formula: =$C1=1 Format: Red Fill Applies to: =$G:$J Formula: =$C1=2 Format: Red Fill Applies to: =$G:$J
    • Post Points: 21
  • Wed, Jul 29 2015 1:37 PM In reply to

    • Hannah
    • Top 75 Contributor
    • Joined on Sun, Feb 7 2010
    • Inglis
    • Posts 12
    • Points 188
    I've just tried putting it in and nothing happens. I've attached a picture of what I did based on your above conditions. Have I done something wrong?? I can always re-attach the spreadsheet with the conditions in it should the picture not work.
    • Post Points: 21
  • Wed, Jul 29 2015 1:43 PM In reply to

    Re: Conditional Formatting Help

    The attachment isn't working and I don't think the any will. can you try to type in what you had?
    • Post Points: 21
  • Wed, Jul 29 2015 1:47 PM In reply to

    • Hannah
    • Top 75 Contributor
    • Joined on Sun, Feb 7 2010
    • Inglis
    • Posts 12
    • Points 188

    Re: Conditional Formatting Help

    Sure thing - sorry my mistake, I thought it was just my computer that was being squiffy. I did the following: Working in cell G10 Formula: =G10<>"" Format: No Color Fill Applies to: =$G:$J Formula: =$C10=1 Format: Red Fill Applies to: =$G:$J Formula: =$C10=2 Format: Red Fill Applies to: =$G:$J
    • Post Points: 21
  • Wed, Jul 29 2015 1:53 PM In reply to

    Re: Conditional Formatting Help

    Do you want it only in row 10 or the entire column of C and G-J? If you want only row 10 add a 10 to the G and J like this. $G10:$J10 If you want the whole column change the $G10 to $G1 and $C10 to $C1.
    • Post Points: 21
  • Wed, Jul 29 2015 2:05 PM In reply to

    • Hannah
    • Top 75 Contributor
    • Joined on Sun, Feb 7 2010
    • Inglis
    • Posts 12
    • Points 188

    Re: Conditional Formatting Help

    I would want the red to be highlighted in the row that the value is present. So for example: if 1 day is entered in C10, then G10-J10 would need to turn red, if there is no value in those cells. If someone put information into those cells, then the red would clear away. I guess we are trying to show customers what cells they need to fill out based on if they enter 1 or 2 days. Then when they fill those required cells out, it looks completed. Would be way easier to explain with seeing the spreadsheet darn it!
    • Post Points: 21
  • Wed, Jul 29 2015 3:22 PM In reply to

    Re: Conditional Formatting Help

    Try changing the G10 to G1 and the $C10 to $C1. See if that helps you. Also when you say 1 or 2 days what exactly are the entering? Dates or the numbers 1 and 2?
    • Post Points: 5
Page 1 of 1 (10 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.