How to Format Excel Tables with Varying number of ROWS

[LoraHut]

Hello!
So I have to take data from multiple tables that all have a varying number of rows, but I want the copied and re-formatted table to all maintain the same layout.
For example, here is an example of one of the raw data tables:


Name of Refinery
Title of Report
Crude Average 08Q3 08Q2
Type A x x
Type B x x
Type C x x
Type D x x x
TOTAL CRUDE x x x

Other Feeds
IP 1 x x
IP 2 x x
TOTAL OTHER x x x

TOTAL INPUTS x x x

And I copy and paste the data into a separate worksheet and arrange it so it is easy to read (like I format using BOLDS for the TOTALs, and highlight each separate section, like CRUDE, with a different background color).
The problem is that I want to format many of these tables in the exact same manner (so that the CRUDES section of the table always has the same color background and border) but from one raw data table to the next, the number of crudes and/or feeds change (so the number of rows that need to be highlighted and colored accordingly change as well)
What code can I use to ensure that if I were to format ANY table (regardless of varying crude and/or feed row number) that they would all have the same general format?
So Row starting with Crude down to Row contained TOTAL CRUDE has a border around it and is a certain bacgrkound color (and totals are bold)
Row starting with OTHER FEEDS down to Row containing TOTAL FEEDS has a border around it and is another different bg color (And totals are bold)
Row containing TOTAL INPUTS has yet another border and another different bg color
(but the number of crude in the list and the number of feeds in the list always vary)


Thank you!

 

[hsk]

If i understand your data correctly ....
Ur 3rd row is a title row for a say Detail section which ends with "TOTAL CRUDE". Every row within these two, you want to format it with diff color / border etc ...... right ?

One way is conditional formatting .....
(I am assuming ur data starts from column A)
Select Cell A1, select the entilre sheet - Go to Format - Conditional Formatting - Select Formula from the dropdown ....
Paste this formula -

=AND(ROW(A1)>MATCH("Crude",$A:$A,0),ROW(A1) < MATCH("TOTAL CRUDE",$A:$A,0))

Select ur format , like say background color = yellow

This will paint all the rows below ur Title row (cell with value = Crude) and above the summary row (cell with value = TOTAL CRUDE) with background color = yellow

U can add similar conditions for other sctions aswel,

Hope this helps