VBA Conditional formatting with cell offset

This is for use on about 10 different "fruits" so i can't use the inbuilt conditional formatting

Why do you say that?

http://office.microsoft.com/en-gb/ex...010217629.aspx

In previous versions of Excel, you could have a maximum of three conditional formats. There’s no such limit in Office Excel 2007; you may have as many conditional formats as you like. The other major limitation of conditional formats in Office Excel 2003 and earlier versions was that Excel stopped evaluating conditional formats as soon as it found one that applied to a cell. In other words, you couldn’t have multiple conditions be true for the same cell! In Office Excel 2007, you can control whether Office Excel 2007 stops or continues after it discovers that a specific condition applies to a cell.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.