Trying to run an if then in a cell based on an if then from another cell

[Manaxam]

I am trying to run an If Then in column J that will tell me weather or not to color a cell in column G with a sub If Then that will change the color of the cell.

I must be missing something because the macro runs without an issue but I'm not getting the desired results .... any help would be appreciated.

    Private Sub Worksheet_Activate()
Dim C As Variant
  Dim CI As Integer
  Dim Col As Variant
  Dim FirstRow As Long
  Dim LastRow As Long
  Dim Rng As Range
  Dim Wks As Worksheet

    Col = "J"
    FirstRow = 2
    Set Wks = Worksheets("Pauline")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
        
        Set Rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
            For Each C In Rng
                If (Range(Cells(FirstRow, Col), Cells(LastRow, Col)) = "Yes") Then
    
                    Col = "G"
                        FirstRow = 2
                        Set Wks = Worksheets("Pauline")
                        LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
                        LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
    
                            Set Rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        
                            If IsDate(C) Then
                                Select Case Int(Now()) - C
                                    Case Is > 90
                                        CI = 3   'Red
                                    Case Is > 75
                                        CI = 6   'Yellow
                                    Case Is > 60
                                        CI = 4   'Green
                                    Case Else
                                        CI = xlColorIndexNone
                                    End Select
                                C.Interior.ColorIndex = CI
                            End If
        
                End If
            Next C

[protonLeah]

Explain: Int(Now()) - C

[Winon]

Hello Manaxam,

Welcome to the Forum.

Why bother with Code? You could use Conditional Formatting for this.

[Manaxam]

protonLeah, The cells I'm coloring are dates and I'm changing the colors based on how past due the dates are compared to today's date (Now).

Winon, because I'm adding to existing code that works (changing the cell colors based on dates) and am only now wanting to change those colors based on changes in another cells.

Thank you both for looking at this.

[protonLeah]

Try:

CInt(Now() - C)

[Manaxam]

Thank you all for your help. Having grown up with Macros it was my only thought but I on suggestion I went with conditional formatting and after playing with am a true believer. Again, thank you.

[Winon]

Hello Manaxam,

I went with conditional formatting and after playing with am a true believer

Yeah, easy!

Glad you got it sorted out!