Code or conditional formatting

[CloudenL]

Not sure which is the best option.

I have a spreadsheet with 2000+ lines (not that all will be used) that are filled in with data from an input sheet. in 1 cell in each row f11 (11 is the starting row) will be a color (blue, purple... etc) based on the data from the input sheet. I have 12 color choices also. I need to highlight the entire row, 42 columns in each row need to be highlighted.

I can do the conditional formatting to make this happen, but was wondering if there is code that would make this easier and less able for someone to mess up as well.

Not sure if i've explained this correct, but am providing a view of the spreadsheet in question to see if it helps.

[Kenneth Hobs]

CF is the better choice.

[CloudenL]

Thank you.

[CloudenL]

Another question then. i put the CF in cell A11:A12 (Merged Cell) to look at F11 and if word is Blue then i want A11:AP12 to be highlighted blue. the problem is when i use the format painter to copy it down to the other cells in A, it says A13:A14, why wont it copy exactly as i put it to change the entire row? Am i doing something wrong or is this just how CF works?

[Kenneth Hobs]

What was the formula? Did you use semi-relative, =$F11>50, or absolute addressing, $F$11>50?

Part of the problem is that you are looking at color. Look at values to set conditional colors.

If you are going to use colors, then an event change routine is best since you will need a UDF to get the color.

It is not good to mix both CF and event methods that check interior cell color.

[CloudenL]

=$F12="Gray" is the formula. the color word comes from chosing a specific product on a previous sheet. so the word Gray shows up on the page. so, the CF is if the word is Gray, then the box would be filled in with Gray and so on for about 12 color choices. but i want the entire row to be filled in with the color. Does that make sense?

[Simon Lloyd]

Posting a sample workbook would help but the merged cells may be giving you the issue - merged cells always cause trouble somewhere along the line

[CloudenL]

Ok, here is a sample the first 2 lines have the CF i need. if i use the painter to copy to lines 3 & 4 it doesnt include the entire row just that one cell.

thank you for any assistance with this.

[Kenneth Hobs]

Select both of the rows and then set your rules for =$F2="BLUE" and set the format as Blue. Set all the rules. Then copy those rows 2 and 3 and paste special as formats to the other rows.

[CloudenL]

Thanks again. I will try that