+ Reply to Thread
Results 1 to 6 of 6

highlight cell if no entries or more than one entries are made

  1. #1
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    256

    highlight cell if no entries or more than one entries are made

    1. I want to highlight cell to yellow if no entries made in AG::AI
    2. I want to highlight cell to red if more than one entries are made in AG::AI

    here's the sheet I am working on

    QUESTION_MARKED IF MORE THAN ONE.xls


    Thanks in advance

  2. #2
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: highlight cell if no entries or more than one entries are made

    You need conditional formatting for this.

    Please find attached the workbook. I have added the conditional formatting for your reference.

    Thanks,
    V
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    256

    Re: highlight cell if no entries or more than one entries are made

    Quote Originally Posted by vikas.bhandari View Post
    You need conditional formatting for this.

    Please find attached the workbook. I have added the conditional formatting for your reference.

    Thanks,
    V


    Hi,

    Thanks for your answer but I do have follow up question.

    I followed your advice and it worked except that I do not know how to copy the same formatting for the next rows (row 5,6...etc.)
    I tried to copy - paste, or dragging, format painter but the format just copy-pasting the exact format and not changing the reference value
    ex:
    =COUNTIF($AG$4:$AI$4;">0")>1
    =COUNTIF($AG$5:$AI$5;">0")>1
    =COUNTIF($AG$6:$AI$6;">0")>1
    =COUNTIF($AG$7:$AI$7;">0")>1

    but what happens is:
    =COUNTIF($AG$4:$AI$4;">0")>1
    =COUNTIF($AG$4:$AI$4;">0")>1
    =COUNTIF($AG$4:$AI$4;">0")>1
    =COUNTIF($AG$4:$AI$4;">0")>1

    unless I am changing the reference manually

    Note: the conditional formatting you gave is in AF but the formatting I did was in AG:AI
    I attached the copy of the sheet

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: highlight cell if no entries or more than one entries are made

    No, you dont have to do it manually.

    You just need to change the target range, and tweak the formula a little so the relative references are being placed correctly in the formula.

    Thanks,
    V
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    256

    Re: highlight cell if no entries or more than one entries are made

    Quote Originally Posted by vikas.bhandari View Post
    No, you dont have to do it manually.

    You just need to change the target range, and tweak the formula a little so the relative references are being placed correctly in the formula.

    Thanks,
    V
    Hi,

    thanks for answering...
    but seems like I am still having problem or I am just doing it wrong...
    I used the conditional formatting formula you used:
    =AND(LEN($AF4)>0,COUNTIF($AG4:$AI4,">0")>1) -> formatted to yellow
    =AND(LEN($AF4)>0,COUNTIF($AG4:$AI4,">0")=0) -> formatted to gray


    but everytime I copy-paste or drag or use format painter, the reference range does still stays as $AG4:$AI4 instead of $AG5:$AI5, $AG6:$AI6...etc.

  6. #6
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: highlight cell if no entries or more than one entries are made


    but everytime I copy-paste or drag or use format painter, the reference range does still stays as $AG4:$AI4 instead of $AG5:$AI5, $AG6:$AI6...etc.
    You don't need to copy. Go to Conditional Formatting --> Mange Rules, select the conditional formatting you are looking to modify and then just change the Applies to value. The number of cells you include here will have the conditional formatting applied automatically.

    Am I not understanding anything?

    Regards,
    V

+ 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