Macro to change color of the cell of specific column

[ashish mehra]

Hi,

I want a code that will change the color of the 'Sales Date" column depeding on the condition.

Condition:
if the Sales Date is less than 2 years fill the cell with Green,
if between 2 - 5 years fill the cell with Yellow and if more than 5 years than Red.

Regards,
AM

 

[SirJB7]

Hi, ashish mehra!
Do you actually need VBA code for this? You can apply conditional format, check the uploaded file.
Regards!

 

[ashish mehra]

Hi, ashish mehra!
Do you actually need VBA code for this? You can apply conditional format, check the uploaded file.
Regards!

No Sir
Solution by VBA code is required. It's a test.

 

[Derek McGill]

If it's a test then maybe you should do it your self ?

 

[ashish mehra]

It's an open book test

 

[Chihiro]

Open book doesn't mean get ready made solution from someone else...

 

[ashish mehra]

I respect your views but it depends on how you take it

 

[Chihiro]

Definition of Open Book test...

Open book exams allow you to take notes, texts or resource materials into an examsituation. They test your ability to find and apply information and knowledge, so are often used in subjects requiring direct reference to written materials, like law statutes, statistics or acts of parliament.

 

[ashish mehra]

OK Chihiro n thanks for definition.

 

[SirJB7]

Hi, ashish mehra!
Put this code where it goes, then run it.

Option Explicit

Sub ReinventingTheSquaredWheel()
    Dim I As Integer, J As Integer, K As Long
    '
    Range("A2:B12").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    '
    For I = 2 To 12
        K = Year(Now() - Cells(I, 1).Value) - 1900
        Select Case K
            Case Is <= 2
                Range("A" & I & ":B" & I).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 5296274
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            Case Is <= 5
                Range("A" & I & ":B" & I).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            Case Else
                Range("A" & I & ":B" & I).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
        End Select
    Next I
End Sub

Good auto cheat.
Regards!

 

[ashish mehra]

Sir JB7

You are a nice man!

 

[SirJB7]

Hi, ashish mehra!
Glad you solved it.
Thanks for your feedback but let me disagree. A proof? Read me at other threads started by you.
Welcome back whenever needed or wanted. And that doesn't mean "always before ever trying to solve a test".
Regards!

 

[ashish mehra]

Thanks Sir JB

I will make note of your kind words.