Menu
Ask a question Report

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

Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
 -
Excel Ask a question Report
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 

4 replies

Best answer
approved by Aoife McCambridge on Nov 26, 2019
Report
Posts
2569
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 25, 2019
 377
2
Thank you
Here it is:


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

Say "Thank you" 2

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

CCM 5801 users have said thank you to us this month

Report
deeptis
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
TrowaD
Posts
2569
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 25, 2019
 377 -
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
Report
Posts
2569
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 25, 2019
 377
1
Thank you
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
Report
Posts
2569
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 25, 2019
 377
0
Thank you
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
Report
Posts
6
Registration date
Thursday June 19, 2014
Status
Member
Last seen
July 25, 2014
-1
Thank you
thank you Trowa, but how can I format the entire row based on a result from a previous cell?
Report
TrowaD
Posts
2569
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 25, 2019
 377 -
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:
http://www.contextures.com/xlCondFormat02.html

Best regards,
Trowa
Report
deeptis
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
TrowaD
Posts
2569
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 25, 2019
 377 -
Hi Dee,

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

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