[SOLVED] How do I change the font color of an entire row auto when a specific is added?

I want to be able to change the font color(red) of an entire row on Sheet1 automatically, if a specific entry is added in a particular column. I know I will need to have a list of all of the items I want identified. I am hoping to put this list on Sheet2. Is that possible or will the list need to be on Sheet1? Either way, how can I accomplish this?

For example:
I have a spreadsheet that contains information about housing. If I am inputing data and enter "Orange County", I want the font color of that row changed. Can I make a list of counties that I want to avoid on Sheet2 and build that list as I go?

I hope this is clear enough. Thanks in advance.

{{SOLVED}}

Are you familiar with conditional formatting?

Well that is exactly what you need, although if you wanted to prevent certain things from being allowed in certain cells you can use data validation and a list.

Google both of those and you should find an answer, otherwise post a sample data workbook and we can help you further.

Yes you can use lists from other sheets.

Post the sample data set as explaining the solution is more work than doing it and letting you look at the answer.

Sheet1
Address City County Sub-Division
11 CAMELLA LN TAMPA Hillsborough
94 SEDGEFIELD ST BRANDON Hillsborough
13 CREEK EDGE DR RIVERVIEW Hillsborough
51 DUNHAM STATION DR TAMPA Hillsborough


Sheet2
Sub-Division
Stoney Brooke
Highlands Phase V
Traditions at Edgewood


So, If I am entering data into Sheet1, D2 {the blank cell under Sub-Division] and I type Stoney Brooke, I want Sheet1,Row2 FONT to turn red. If I type Marshmallow in that same place, I don't want it to do anything. I want whatever is in my column on Sheet2, Column A (excluding the header)to dictate if Sheet1 will have a red Font row.

I hope that's a little more clear.

{That looks terrible}

Nope, ATTACH A WORKBOOK not data in text in this forum. I dont have the patience to digest it on the forum but I DOOOO like playing with IN EXCEL.

I also wouldnt bother with the conditional formatting, but I would use data validation to not allow things to be entered without being on the "approved" list.

Either way you can make it work but Im not typing any more.

Please excuse my noob-ness to the forum. Redflag_Sub.xlsx

What I'm trying for is to make the row red if an item from my redflag list is entered into the subdivision column. If it's not on the list then I don't want it to do anything.

Thank you for your patience.

You need to remove the blanks in Subdivision as shown:

Redflag_Sub-updated.xlsx

EDIT: and your conditional formating is shown here,

Sorry I have a busy day and didnt explicitly type that out

LOL, yes I am familiar with how to delete things in excel

I deleted them and added the conditional formatting in the excel file above,I was jut letting you know you had to do it in order to use the simple logic.

The formulas are there to make it work, it seems I am still missing your point which is why it is ideal to mock up a before and After with flat data and flat outcome.

Change the formula to work with the appropriate column, sorry I have meetings to go to.

The formula is in the Conditional Formatting, Manage Rules. This one formula covers the whole range from that you have. I have extended the range down to row 200 and added Data Validation so that Subdivisions not in the list will not be allowed. When a valid entry is made, the row will turn red.

The Conditional Formatting formula can be shortened to:

Formula:

Please Login or Register  to view this content.

In the file that I uploaded, change the $A$194 to $a$200 (I made a slip when selecting the range)

Why do you keep asking us if the reference needs to be on the same sheet when you could and should test it out yourself in excel?

You will need some logic to aggregate your list from the subdivision tab to remove all the 0s in order to create a clean CF list to appy the rules.

The CF doesn't have to be on the same worksheet.

To clear the Data Validation, select column D, click on the Data Tab, when prompted to extend the selection select NO then click CLEAR ALL.

To explain:

Newdoverman, when I opened your upload there was not a rule added that I could “manage”. So, I selected Column D, created a new rule and pasted your formula exactly as is into [Format values where this formula is true:] and changed the format to red. Nothing happened.

So, I went back and looked at the CF rules mgr. and selected edit. This is what was there
[="MATCH($D2,Subdivision!$A$2:$A$200,0)"] without the brackets.

I removed the quotation marks (because I didn’t know what else to do) and hit OK.
I rcv’d the message “You cannot use references to other worksheets or workbooks for Conditional Formatting criteria”.

I moved my column to the same sheet and changed the reference in the formula to [="MATCH($D2,$G$2:$G$200,0)"], nothing happens. I removed the quotation marks, leaving my test data in place, hit OK and the entry above my target item turns red.

Also, I am not prompted by anything when I select the Data Tab.

Got to be honest I didn't think you could reference external data for a conditional format.

I have attached what I think you are after, albeit with the subdivision list on the same sheet and with no validation.

HTH

Dave H

I have no idea what you are doing. Perhaps, the active cell that you had selected was not in column D. I downloaded the file that I uploaded to you and it works perfectly. Here is the file again with the Data Validation removed. There is no problem at all with the list being on another worksheet within the workbook.

Originally Posted by newdoverman

I have no idea what you are doing. I downloaded the file that I uploaded to you and it works perfectly. Here is the file again with the Data Validation removed.

I opened your file previously and it was working as designed. What version of Excel are you using Admobeer?

It must be a version conflict between Excel 2007 and Excel 2010 that I use because I downloaded the file I sent to you and I can't make it fail and the rule is listed under Conditional Formatting, Manage Rules.

Admobeer,

Try the attached using vba which will allow the subdivision on an different worksheet. You need to enable macros and note that it is case sensitive.

Hope that sorts it

Dave H

That is the way Excel 2010 works. You have to enable editing in order to edit.

There must be a conflict in how the two versions of Excel use Conditional Formatting.