Thread: Complex Conditional format by macro plus count

Hiya, guys don't often post as i usually know what i'm doing, i have quite abit of experience but not enough.
I'm using a conditional format macro to apply colour (yes Uk) to cell dependant on value, but can not get it to work correctly as the value's are dates, i have several coloumns that need formating in different ways such as follows table range: g8:s27
Range g8:g27 - no value red format, anydate green.
Range h8:h27 - more than 3 years of current date red, less than ... green
same for <> 1 and 2 years.

the macro i'm using is :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Long
Dim cRange As Range
Dim Cell As Range
   '***************** check range ****
   Set cRange = Intersect(Range("g8:g27"), Range(Target(1).Address))
   If cRange Is Nothing Then Exit Sub
                       
   For Each Cell In Target
     vLetter = UCase(Left(Cell.Value & " ", 1))
     Select Case vLetter
        Case ###(anydate)###
            vColor = 4
        Case Else
            vColor = 3
     End Select
     Application.EnableEvents = False   'should be part of Change macro
     Cell.Interior.ColorIndex = vColor
     Application.EnableEvents = True   'should be part of Change macro
   Next Cell
     'Target.Offset(0, 1).Interior.colorindex = vColor
     ' use Text instead of Interior if you prefer
End Sub

The ###(anydate)### part i need to find out along with if it's more than 3 years than current date.

I've also seen several countif macro's to count how many red cells there are in the table range, yes i've done this before but i can't remember where i put it to refer back to, and the several i've recently used don't fully work mainly due to the conditional formats.
I've also tried using the formula countif but for some unknown reason doesn't like "<a6" for example which is a date that's three years less than the current.

Just don't think i have my head screwed on today.
Any help much appreciated
Regards Duck