Excel Help

[raghu_1968]

Attached the Excel sheet Tracker and needed help on conditional formatting.

Wanted to use the sheet for tracking purpose.

In the column "J" if I select from the drop box, then the entire row has to be filled with the selected color.

I have tried and not able to complete. Can any one help to complete the same and point out where i have missed?

Regards,
Raghu.

 

[Michael_M]

If I understand correctly, you want to use the 'conditional' formatting in the Home tab. You want to focus on the cell (J2) and click on the conditional formatting. Select "Highlight Cell Rules" then "Equal to..." and in the corresponding box, type the status name (i.e. "Under Progress") and select what formatting you want. Test to see if this works. If it does, in conditional formatting, clear all formatting from entire sheet. The above was just to test.

You can select the column J and repeat for each different formatting you want (i.e. each specific name of Status).

You may need to clear all formatting from the entire sheet first before you test.

 

[ChrisM]

"In the column "J" if I select from the drop box, then the entire row has to be filled with the selected color."
Do you mean that you want the entire row to have the coloured background, or only columns A to J or A to K?
It can be done, I've done this on a spreadsheet at a former employer but I found it really difficult to get the conditional formatting right, to do what you want it to do. These are the notes that I have on how to do it:

Select the cells that you want to format conditionally. If more than one, the top left cell should be the active cell in the selection. Note the address of this cell.
On the Home tab of the ribbon. select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter a formula that evaluates to TRUE (or a non-zero number) if the rule should be applied, and to FALSE (or 0) otherwise.
In your example, let's say A2 is the active cell in the selection.
Use the following formula:
=B2<>""
Click Format...
Activate the Fill tab.
Specify the desired colour.
Click OK, then click OK again.

 

[raghu_1968]

"In the column "J" if I select from the drop box, then the entire row has to be filled with the selected color."
Do you mean that you want the entire row to have the coloured background, or only columns A to J or A to K?
It can be done, I've done this on a spreadsheet at a former employer but I found it really difficult to get the conditional formatting right, to do what you want it to do. These are the notes that I have on how to do it:

Select the cells that you want to format conditionally. If more than one, the top left cell should be the active cell in the selection. Note the address of this cell.
On the Home tab of the ribbon. select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter a formula that evaluates to TRUE (or a non-zero number) if the rule should be applied, and to FALSE (or 0) otherwise.
In your example, let's say A2 is the active cell in the selection.
Use the following formula:
=B2<>""
Click Format...
Activate the Fill tab.
Specify the desired colour.
Click OK, then click OK again.

Thanks ChrisM. I followed the same methodology and mot able to get it. I am trying to attach the excel sheet as such. Can it be possible to attach the excel sheet which i worked instead of providing the link.

 

[ChrisM]

I find Excel conditional formatting very frustrating and temperamental. I've followed my notes using your spreadsheet and also gone to a few online tutorials and what they say does not work. However, this one does but you obviously have to adapt the instructions and substitute "Pending" for "Bob" and repeat the rule for the other categories where you want the row highlighted:

Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)

Want to highlight rows based on a cell value? In this tutorial, I will show you how to use Conditional Formatting to highlight rows in different scenarios.

trumpexcel.com

I've attached an example for "Pending" (I hope that the upload works, not done this before)