Macro to look at another cell, check for text and amend the fill of the current cell

Hi guys.

As well as other things, I have code in play that auto-sums (sub AutoSum) in Column F the count of cells up until a new blank row appears, then formats the cell with the sum with an orange (RGB(204, 102, 0)) fill.

Then I use the code below to change the fill to green if the value is less than 14:

Please Login or Register  to view this content.

The problem is I need the second format change (listed above) to be altered slightly, or replicated and adjusted in another line of code, where if the word 'SAMP' sits anywhere in the cell (always in Column B) that is 1 row up and 4 columns behind the cell having the sum added to it (always in Column F) that the colour should change to green only if the value is less than 12 (not 14).

In my attachment, an example of this would be that F12 should not be green, as for these SAMP products (SAMP is found in cell B11) the maximum should be 12. Therefore is should stay orange, or go back to being orange.

I believe I need to use range offsets and combine them with some sort of 'Like' or 'InStr' code but I can't work out how to do it.

If this is confusing, sorry!

Thanks heaps.