I am trying to run through every row in column A that has a value. As I go through each row I want to do an action based on the value a user selects from a validation list I already created with values (Critical, High, Low). If user selects Critical in A1, then I want F1 to be set to red. If High, then F1 set to yellow, if Low, then F1 set to Green. I keep getting an object error, but I am not sure why. Any help would be appreciated.
Sub Changecolorofcell()
Dim mRow As Long
Dim mColumn As Long
Dim Mark As Range
Dim CellValue As Object
Dim R As Range
mRow = 0
mColumn = 0
Set R = Cells(mRow, mColumn)
Set Mark = ThisWorkbook.ActiveSheet.Cells(mRow, mColumn)
Do While R.Value <> ""
Set CellValue = Mark.Value
color (CellValue)
mRow = mRow + 1
R = mRow + 1
Loop
End Sub
Function color(CellValue As String) As Variant
Select Case CellValue
Case "Critical"
Range.Cells(mRow, 5).Select
With Selection
.Interior.color = 59345
End With
Case "High"
Range.Cells(mRow, 5).Select
With Selection
.Interior.color = 59311
End With
Case "Low"
Range.Cells(mRow, 5).Select
With Selection
.Interior.color = 59323
End With
End Function