Conditional formatting: Format multiple rows based on same criteria

**sempera** · 12-12-2011, 05:06 AM

Hi,

I am have a table with 4 columns and I need to color cells in each row from lowest to highest number. I've done that using conditional formating.

The trickly part is that I have several hundred rows that I need to apply that rule to and I will have to do that quite often.. I know I can do it by using format painter and then paste rule to each row manually, but is there a faster way?.

Thank you.

M.

**arlu1201** · 12-12-2011, 05:20 AM

Attach a sample of your file so we can understand your data better and offer you a quick solution.

**sempera** · 12-13-2011, 11:06 AM

there is my data, i need to color just cells with numbers of course.

**NBVC** · 12-13-2011, 11:13 AM

Assuming this is Excel 2007 you are working with... then go to E1, and go to Home tab|Conditional Formatting|Manage Rules. You just need to change the Applies To range to encompass the whole range, i.e. =$E$3:$H$41

**sempera** · 12-17-2011, 05:11 PM

Got it to work, its not totally automatic, but its still quite fast. The tricky part for me was that I had to compare each row individually and some parts of the data had more columns than the others.

What I did at last was set multiple conditional rules to the first row I had to start with ( I Selected more columns in that row than the widest data range in my worksheet). I didnt want to lose my formatting.

Rules were:
1. For cells that are empty I chose a format without borders.
2. For cells that were not empty I chose a format with borders that I had.
3. I chose a coloured rule that I wanted.

Then i pressed Format painter twice and just held down arrow till the end of the range.

Then at the start of the next range I clicked on first row and down arrow again. Etc.