Hi, I am a new excel user and vb program. I managed to code my excel worksheet based on my prefered criteria/condition/formulas. But i am experiencing this error:28 out of stack problem. Can someone help me with this as i have already google for every solution, but still can fix it.
here are the codes i have so far.
Sub colouring()
Dim cell As Range
'initialise Row flag
Let Rowno = 1
'column D (Target Feedback Date)
For Each cell In Sheet1.Range("D2:D" & Sheet1.Range("D" & Rows.Count).End(xlUp).Row)
Rowno = Rowno + 1 'increment row count
If IsDate(Sheet1.Range("F" & Rowno).Value) = True And _
(Sheet1.Range("F" & Rowno).Value <= cell.Value Or _
Sheet1.Range("F" & Rowno).Value = Date) Then 'font blue if AFD = Now
cell.Font.ColorIndex = 5 'blue 'and AFD <= TFD
Sheet1.Range("E" & Rowno) = (cell.Value - Date) & " more days for feedback." 'text display in column E
Sheet1.Range("E" & Rowno).Font.ColorIndex = 5 'blue
ElseIf IsEmpty(Sheet1.Range("F" & Rowno).Value) = True Or _
Sheet1.Range("F" & Rowno).Value > cell.Value Or _
cell.Value = Date Or cell.Value - Date <= 1 Then 'font red if AFD empty,
cell.Font.ColorIndex = 3 'red '1 day before or <= TFD
Sheet1.Range("E" & Rowno) = "Exceed Target Feedback Date"
Sheet1.Range("E" & Rowno).Font.ColorIndex = 3 'red
End If
If IsDate(Sheet1.Range("I" & Rowno).Value) = True Or cell.Value - Date > 1 Then
cell.Font.ColorIndex = 0 'black 'font black = case close
Sheet1.Range("H" & Rowno) = "Case Closed." 'or TCD > Now
Sheet1.Range("H" & Rowno).Font.ColorIndex = 0 'black
End If
Next cell
Let Rowno = 1
'column G (Target Closing Date)
For Each cell In Sheet1.Range("G2:G" & Sheet1.Range("G" & Rows.Count).End(xlUp).Row)
Rowno = Rowno + 1
If IsEmpty(Sheet1.Range("I" & Rowno).Value) = True And _
cell.Value - Date <= 2 Then
cell.Font.ColorIndex = 3 'red 'font red if ACD empty or
Sheet1.Range("H" & Rowno) = "Exceed Target Closing Date" '2 days before or <= ACD
Sheet1.Range("H" & Rowno).Font.ColorIndex = 3 'red
Else 'font black = case close
cell.Font.ColorIndex = 0 'black
Sheet1.Range("H" & Rowno) = "Case closed." 'text display in column H
Sheet1.Range("H" & Rowno).Font.ColorIndex = 0 'black
End If
Next cell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Call colouring
End Sub
Private Sub Workbook_Open()
Call colouring
End Sub
And attach is the sample excel i'm working on..
Bookmarks