How do i format cell to change colour as the number increases

[Guest]

I want to format a cell to change colours as the total numbers increases from
10 to 20 and 30-100 with red, yellow and blue respectively in Ms excel? Also
if i input a name on a cell, Iwant the cell to format it to indicate if it
has appeared within last six months by indicating red or yellow for less than
six month or more than six month respectively?

 

[stevebriz]

Lanre,
Here is the first part...as below.
for the second part where is the date you comparing today dates with
;relative to the name ?

Eg: enter the name in A2 , and If the date in B2 is more than X months
previous to today then the colour is red or yellow.?
Can you please define where the date you wnat to compare todays is

'--------------------VBA CODE--------Format colors of cell based on
value---------------
'I used Cell Ar as the example
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

With Range("A2")

Select Case Val(.Value)
Case Is < 10
..Interior.Color = vbRed
Case Is < 20
..Interior.Color = vbYellow
Case Is < 100
..Interior.Color = vbBlue
Case Else
..Interior.Color = vbWhite
End Select
End With
End Sub

 

[Guest]

Thank you Stevebriz! The name will appear in A2, The date will appear in A3
which if is less than six month A4 should show yellow colour with 'VALID'
showed on A4 else it should be red with 'expired' shown on A4. The date will
be compare to today date.

 

[stevebriz]

Lanre
Here is your new sub with what you want

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
With Range("A2")


Select Case Val(.Value)
Case Is < 10
..Interior.Color = vbRed
Case Is < 20
..Interior.Color = vbYellow
Case Is < 100
..Interior.Color = vbBlue
Case Else
..Interior.Color = xlNone
End Select
End With
End If

With Range("A3")
If DateDiff("m", .Value, Date) < 6 Then

Range("A4").Value = "VALID"
Range("A4").Interior.Color = vbYellow
Else
Range("A4").Value = "Expired"
Range("A4").Interior.Color = vbRed
End If

End With
End Sub

 

[Guest]

Thank you Stevebriz, but i have not got it clearly, how do i input the
program in excel? please explain it to me like: select the cell you want the
data to appear. type $a$2.......

Lanre

 

[stevebriz]

Lanre,
Is there more cells than jsut A2 -A4 you want this to "effect" to occur
on?
If so please tell me how which cells you want this to affect.
This is code that you put in a through VBA( Press ALT F11) and this
brings up the Vbeditor
You then double click where it says sheet1 in the project explorer and
paste this code in
(This is assuming you are working on sheet 1)
Whenever you change A2 the color will change
The ":valid" will checked when any cell on the sheet is changed....or
do you want this to be automatic when you open the sheet?

 

[Guest]

Please see below, this is all i want to do, I will only need to input the
current date (A3) and get either 'valid' with blue colour or Expired with red
colour in A4
A1 Ade Shola Bex Niyi
A2 3/4/06 4/1/06 23/2/06 26/2/06
A3 31/8/06 31/8/06 31/8/06 31/8/06
A4 'valid' 'Expired' 'Expired' 'Expired'

 

[stevebriz]

I cna make it so it just puts today date if the 3 row if you like? and
thenit will automatically work out the values in the 4 th row (A4)?

 

[Guest]

Thank you! I pasted below on it after double clicking the sheet 1 where i
have the data but nothing shows.

Select Case Val(.Value)
Case Is < 10
...Interior.Color = vbRed
Case Is < 20
...Interior.Color = vbYellow
Case Is < 100
...Interior.Color = vbBlue
Case Else
...Interior.Color = xlNone
End Select
End With
End If

With Range("A3")
If DateDiff("m", .Value, Date) < 6 Then

Range("A4").Value = "VALID"
Range("A4").Interior.Color = vbYellow
Else
Range("A4").Value = "Expired"
Range("A4").Interior.Color = vbRed
End If

End With
End Sub

 

[stevebriz]

Did you save the workbook and reopen and click enable macros after
adding the code?

 

[stevebriz]

Try this one...if should do want you want as below

Private Sub CommandButton3_Click()
Dim j As Integer
On Error GoTo Errhandler
For j = 1 To 4 ' set number columns wide you want If you have 4 names
then this is 4

If Cells(2, j).Value <> "" Then ' check the row 2 for the
date is not empty
If Len(Cells(2, j).Value) >= 8 Then
Cells(3, j).Value = Date ' enters today date in
row 3 for each name

If DateDiff("m", Cells(2, j).Value, Date) <= 6
Then ' checks date is less/equal than/to 6 months
Cells(4, j).Value = "VALID"
Cells(4, j).Interior.Color = vbBlue
Else
Cells(4, j).Value = "Expired"
Cells(4, j).Interior.Color = vbRed
End If
Else
MsgBox " Invalid Date Entry in Vell " &
Replace(Cells(2, j).Address, "$", "")
Cells(4, j).Value = ""
Cells(2, j).Value = ""
Cells(4, j).Interior.ColorIndex = xlNone
End If

Else
Cells(4, j).Value = ""
Cells(4, j).Interior.ColorIndex = xlNone
End If
Next j
Exit Sub
Errhandler:
If Err.Number = 13 Then

MsgBox " Invalid Date Entry in Vell " & Replace(Cells(2, j).Address,
"$", "") ' for if he date is not entered right
Cells(2, j).ClearContents
Err.Clear
Else
End If

End Sub