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
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 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

4 replies

Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
Here it is:


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

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

CCM 2942 users have said thank you to us this month

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.
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
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
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
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
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
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
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?
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
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
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
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
Hi Dee,

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

Best regards,
Trowa
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?