Turn Rows a color depending on cell value

[bamaisgreat]

I am looking for some options.
If I were to Type in the characters APP in a specific cell such as L719 in column L I need the cells in that row from say A719:N719 to turn Green or If the characters were RFC I need those cells to turn Red.

Thanks for any help.

 

[gertie193]

Hello,

You would use Conditional Formatting.

On a smaller scale, if you had data in cells A2 to N30, you would:

• Highlight cells A2 to N30 (this lets Excel know what cells/rows you want to apply the formatting to.)
• --
• Go to Conditional Formatting under the Home tab
• --
• Select 'New Rule'/'Use a Formula to Determine Which Cells to Format'
• --
• Type in the 'Format Values Where This Formula is True' box: = $L2="APP" (this tells Excel when a value in L column is 'APP', then apply the formatting. The reason it says $L2 in the formula is b/c it tells Excel to lock it on column L ($L) and move on down the rows 2,3,4,5, etc. That's why there is no $ in front of the 2. $L2 is just the cell you want the formatting to START at. Excel will apply that rule to all of the cells in the L column b/c you did not put a $ in front of the 2, which would mean you were locking formula to just row 2.)
• --
• Click on Format...and select the color green you want under Fill.

Please let me know if that worked for you, or if you have questions. It worked on my test copy.

EDIT: Re: RFC:

Follow the same steps, but make a new rule with the formula = $L2="RFC". Then click on the Format... button and apply the Fill color red you want.

So, when all is said and done, you will see 2 rules in the Conditional Formatting box.

 

[bamaisgreat]

Thanks Gertie for all the help. Is there a way to lock or protect to keep people from the Conditional Formatting being pasted into an individual cells.

 

[gertie193]

Thanks Gertie for all the help. Is there a way to lock or protect to keep people from the Conditional Formatting being pasted into an individual cells.

Hello,

You can do 2 things that I can think of:

1) lock the cells that contain conditional formatting. When you do this, though, people can select those cells but can't edit those cells. In your case they can copy them, but they can't paste them elsewhere. The section called 'Lock And Protect Selected Cells From Editing With Kutools For Excel' site is a good description on how to do this: https://www.extendoffice.com/documents/excel/866-excel-lock-protect-cells.html or this site: https://www.ablebits.com/office-addins-blog/2016/02/16/lock-unlock-cells-excel/

1. You are basically going to unlock all of the cells (b/c they are locked by default)
2. Select the cells you want to lock/protect
3. Relock the cells
4. Password protect the sheet to enforce this locking (you can leave the password box blank, if you don't think someone go the extra mile to disable the locking of the cells.)

As the website describes, to unlock/unprotect the sheet, just click on the Review tab/Unprotect Sheet.

2) the other way, is to just tell them to go ahead a copy a cell with the conditional formatting, but when they paste it elsewhere, they need to paste as a value. This will remove any conditional formatting.

When you go to paste, just go to the Home tab, click the little black arrow under Paste, and under Paste Values, click the 1st choice called Values. I actually use keyboard shortcuts to do this, but I will just keep the explanation simple.

I hope this helps. Post back if you have questions. I or someone else can try to reply.