Format cells based on cell above for each worksheet in workbook

[qdavhen]

Hi,

I have a workbook with multiple worksheets (continually adding) and I would like a macro that:

For every worksheet in the workbook except for worksheets named "Main" and "Full Detail" to format each cell from cell B3 (Cell B3 compares to Cell B2, but B2 doesnt compare to B1 as B1 is a title) to the last used column and last used row IF the cell above is different.

The macro always needs to ignore the data in columns A and B.

Example (starting at cell B3 which would compare to Cell B2):


xxxxx xxxxxx xxxxxx xxxxxx xxxxx xxxx
xxxxx xxxxxx xxxxxx bbbbb xxxxx aaaa
xxxxx xxxxxx xxxxxx bbbbb xxxxx aaaa
xxxxx xxxxxx xxxxxx bbbbb xxxxx aaaa
xxxxx cccccc xxxxxx bbbbb xxxxx dddd

So in this instance the cell with the "bbbbb" and "aaaa" in the 2nd row and the cells with "cccccc" and "dddd" in row 5 would be formatted red. This macro would continue until all worksheets are checked (except for Main and Full Detail).

I've been able to achieve this manually with conditional formatting and even using macro recorder and format painter, but it causes some problems with other cells as it changes things like date and money formats.

Help would be much appreciated. I'm definitely learning lots from what started out as a simple "project"!

Thank you all.

Dave.

[inesi]

Hi Dave, you can achieve that with conditional format only.

Try the following:
1) On one of the sheets, select from cell B3 to B65536
2) Apply a conditional formating there using the formula below:

Please Login or Register  to view this content.

3) Then apply red color for that condition
4) Repeat the same thing for all other sheets you need (or select column B on that sheet and use the painter to repeat to all other sheets)


But if you really need a macro, try the one below:

Please Login or Register  to view this content.

I hope that will help you

Good luck

[qdavhen]

Hi Inesi,

Thank you for your code. It doesn't do quite what I'm after. It is only applying the macro to Column B. I need it to apply to the whole row of data.

I realised I made an error in my request. I need the macro to apply from Column C (not B as written) to the last used column on each sheet. There is approximately 40 columns (but each sheet is a bit different).

So if the different text is in any cell in any column it will be highlighted.

Thanks

Dave.

[inesi]

Hi Dave, thank you for the clarification. Try the code below.

Adjust StartRow and StartCol as per your needs.

Please Login or Register  to view this content.

Good luck

[qdavhen]

Hi Inesi,

That's perfect! Just what I was after!

Thanks very much!

Dave