lundi 23 mars 2015

VBA: If Cell Value Equals Decimal Then Change Font Colour

I'm looking for what I believe to be a fairly simple code but can't seem to figure it out.


Effectively I want someone to enter values into a predetermined range of cells.


Once they've completed entering their values I want them to hit a "Save Button".


As part of the validation check I want two things to happen:


If their range of cells contains decimal/fractional numbers I want the numbers they've entered in incorrectly to change to colour Red.


And finally, once they've corrected their change I want the numbers to revert back to their original Black colour.


So far I've been trying to workout the first code (unsuccessfully) and haven't been able to think through the second part of the code, help!



Sub ChangeColorNotNumeric()
Dim i As Long
Dim rCell As Range
Dim rRow As Range
Dim rRng As Range

identify the range to search
Set rRng = Sheet1.Range("Hello")


For i = rRng.Rows.Count To 1 Step 10
'loop through all the cells in the row
For Each rCell In rRng.Rows(i).Cells
If Not IsNumeric(rCell.Value) Then
'delete the row and go to the next one
rCell.Interior.Color = RGB(255, 0, 0)
Exit For
End If
Next rCell
Next i

End Sub

Aucun commentaire:

Enregistrer un commentaire