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
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
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
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.
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.
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.
Am I not understanding anything?
Regards,
V
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks