Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Grey out an entire row in Excel 2010 depending on a cell's value [Solved/Closed]

Report
Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
 -
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 -
Hi all,

I am trying to grey out an entire row in excel 2010 depending on a cell's value.

Each cell has a drop down list: Yes, No, Maybe etc

I want the entire row going forward, to only be greyed out if a cell indicates the value No. and the row remains active if other options are selected.

I look forward to hearing your thoughts.

Thanks
Dee
See more
Related:

4 replies

Answer 1 / 4
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 415
Here it is:


Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Thank you
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4677 users have said thank you to us this month

Report
Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
but what would I need to do if cell A1 was not complete but B1 was complete, I would need the formula to be transfer across the entire row and skip to the next cell if the outcome was not complete.
Report
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 415
Hi Dee,

So you want to grey out the entire row whenever "Complete" is found within that row at any location. Then use this formula:
=ISNUMBER(MATCH("Complete",1:1))

Best regards,
Trowa
Answer 2 / 4
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 415
Hi Dee,

What you are trying to do is called conditional formatting.

Which can be found on the Home tab, in the Styles group. Go to New rule and put in your conditions.

Let us know if you get stuck.

Best regards,
Trowa
Answer 3 / 4
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 415
Hi Dee,

Guess what: I just switched to Excel 2013!

You can either pre-select the range you want to apply conditional format to before applying, or you can click on the conditional format button and go to bottom option (Manage rules).
Here you can see the range the conditional format is applied to.

To further assist you I made a screen capture (working with a Dutch version) of the "Manage Rules" screen where you can see that the CF is activated if "Completed" is found in column A and it is applied to the first 20 rows.

[Picture won't show, I'll try in separate post.]

Best regards,
Trowa
Answer 4 / 4
Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
thank you Trowa, but how can I format the entire row based on a result from a previous cell?
Report
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 415
Hi Dee,

Since I'm using Excel 2003, I can't tell you from personal experience.

So let me provide you with a link which shows both a video and text of what you are trying to achieve:
https://www.contextures.com/xlCondFormat02.html

Best regards,
Trowa
Report
Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
thanks for this.

I am trying to track progress of a projects, so each week an option is selected until the option 'complete' is selected, once that has been selected, I want the entire row to the right of the 'completed' cell to go grey.

Is this possible?

Thanks
Dee
Report
Posts
2614
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 7, 2020
 415
Hi Dee,

In the example they use the formula =$B2>75. You will want to use the formula =$B2="Complete".

Best regards,
Trowa
Report
Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
thank you, but how do I ensure the entire row is then formatted? at the moment it is only the cell below the 'completed' cell that is formatted?
Ask a question