comparing values from cell to cell

  • Thread starter Thread starter johnnyray00
  • Start date Start date
J

johnnyray00

Here is what I'd like to do. In one column I have five different value
to chose from D (Daily), W(Weekly), M(Monthly),SA(SemiAnnual), an
A(Annual). If that value is cell A1 is D, for example, I need t
subtract a start date from a stop date (which are in two other cells o
same row). If the total days equal 2 or less, I want to format the ro
as green. If more than 2, then make it red.

The (timeframe) are as follows :

D = 2
W= 7
M = 30
Q = 45
SA= 90
A = 180

I don't think I need the whole code. If anyone can give me an exampl
using one timeframe, I problably can get it from there.

Thank
 
Johnny

one way:

Sub ColourRows()
Dim LastRow As Long
Dim i As Long
Dim NumberOfDays As Integer

LastRow = Range("A65536").End(xlUp).Row

For i = 2 To LastRow
NumberOfDays = Range("D" & i) - Range("C" & i)
Select Case Range("A" & i).Value
Case "D"
If NumberOfDays > 2 Then
Range("A" & i).EntireRow.Interior.ColorIndex = 3
Else
Range("A" & i).EntireRow.Interior.ColorIndex = 4
End If
Case "W"
If NumberOfDays > 7 Then
Range("A" & i).EntireRow.Interior.ColorIndex = 3
Else
Range("A" & i).EntireRow.Interior.ColorIndex = 4
End If
Case "M"
If NumberOfDays > 30 Then
Range("A" & i).EntireRow.Interior.ColorIndex = 3
Else
Range("A" & i).EntireRow.Interior.ColorIndex = 4
End If
Case "Q"
If NumberOfDays > 45 Then
Range("A" & i).EntireRow.Interior.ColorIndex = 3
Else
Range("A" & i).EntireRow.Interior.ColorIndex = 4
End If
Case "SA"
If NumberOfDays > 90 Then
Range("A" & i).EntireRow.Interior.ColorIndex = 3
Else
Range("A" & i).EntireRow.Interior.ColorIndex = 4
End If
Case "A"
If NumberOfDays > 180 Then
Range("A" & i).EntireRow.Interior.ColorIndex = 3
Else
Range("A" & i).EntireRow.Interior.ColorIndex = 4
End If
Case Else
' do nothing
End Select
Next 'i
End Sub

Regards

Trevor
 
Try this. I have changed SA to require just S. Tried to make it
function but discovered that we cannot change interior colour fro
within a function.


'----------------------------------------------
Sub TimeFrame()
tfstring = "DWMQSA"
rw = 1
While ActiveSheet.Cells(rw, 1).Value <> ""
tfarray = Array(0, 2, 7, 30, 45, 90, 180)
dif = Cells(rw, 3).Value - Cells(rw, 2).Value
ActiveSheet.Cells(rw, 4).Value = dif
tf = tfarray(InStr(1, tfstring, ActiveSheet.Cells(rw, 1).Value
1))
colour = IIf(dif >= tf, 3, 35)
Range(Cells(rw, 1), Cells(rw, 3)).Interior.ColorIndex = colour
rw = rw + 1
Wend
End Sub
'-----------------------------------------
 
Back
Top