PDA

View Full Version : Code or conditional formatting



CloudenL
10-27-2011, 10:47 AM
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
10-27-2011, 08:49 PM
CF is the better choice.

CloudenL
10-28-2011, 05:35 AM
Thank you. :)

CloudenL
10-28-2011, 11:12 AM
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? :confused:

Kenneth Hobs
10-28-2011, 11:50 AM
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
10-31-2011, 09:19 AM
=$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
10-31-2011, 09:29 AM
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
10-31-2011, 01:05 PM
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
10-31-2011, 01:27 PM
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
11-01-2011, 06:19 AM
Thanks again. I will try that