If this = 'No' then change background color

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
Is there a way to say if this cell = No then change the background color to gray to highlight the fact that you no not need to do this. The sheet is getting VERY big and on little cell contains a 'Yes' of 'No' and if it's 'No' I would like to gray the whole line. :biggrin:

Thanks,
Bruce.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Use conditional formatting.

  • Select your data.
  • Go to Format\Conditional Formatting
  • If the Yes/No values are in colum D, you would use

    Formula is | $D1="No"

    (where D1 is the first row of data)
  • Set the color by the options available when clicking the "Format" button on the dialog.
  • Press OK until you are out of the dialog, and the rows with "No" in column D should be highlighted.
 
Upvote 0
Say A1 contains Yes or No, and you would like to highlight the entire row if No. Activate A1 and click the row header. From the menu choose Format|Conditional Formatting. Condition 1 Formula is:

=$A1="No"

click Format, choose one and click OK twice.

If the Yes/No is in some other cell activate that cell before selecting the row and use that cell's reference instead of $A1 in the formula, making sure you keep the $ before the column letter.

You can copy the Conditional Format to other rows using Copy Paste Special|Formats.
 
Upvote 0
it works but the cutting and pasteing does not this is my formula =$B6="No" and I highlighted the whole row then I did that. When I do the paste special and select formats. If I set B6 on a different row to Yes only that cell B6 changes back to a white background.

I'm on the right track though.
Bruce.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top