VBA Condotional Formatting

[myheadhurts]

I'm using Excel 2002 and am struggling with some conditional formatting.

As I have more than 3 formats I need to achieve, I'm trying to use a VBA code rather than Excel's 'conditional formatting'

I've attached a small example to demonstrate, but basically what I have is a range of cells (sheet 1) which are updated from other sheets (sheets 2 & 3) and I need the background colour of the cells to change, with the colour depending on the cell result.

Book2.zip

I have created a VBA code on sheet 1 which you can see by right clicking the tab and selecting 'view code'. This seems to be working, if you type any of the results i.e. Y4, Y6, Z2 etc into my range B3:AL12 the background changes, but where the result is fetched from another sheet using a formula the cells are not changing.

All I can think is it's because these cells contain a formula.

Any help or advice would be appreciated.

Myheadhurts

[antoka05]

See attached file... I moved your code in sheet2 and sheet3 Worksheet_Change event.

I hope it's what you need.

Regards,
Antonio

[myheadhurts]

Thanks Antonio that works well

I think I've understood how you did it. The VBA code needed to be on the sheet with the source data and not where I wanted the final result.

Could I do a similar thing with data on another sheet, I have a single column of data which is transposed into a number of rows, could I get the rows to pick up the formatting from the column.

I've attached another small sample, as you can see the rows have picked up the 3 colours which are set up in 'conditional formatting', but they haven't got the others. I've had a go at creating the VBA code but It's not working, I think it's the target range I haven't got right.

Book3.zip

Thanks again
Myheadhurts