I am running a private sub with the following in it and is based on a result of a calculation from 2 other cells
Dim r As Range, c As Range
Set r = ActiveSheet.Range("I15:I40") 'change range as needed
For Each c In r
With c
If Not IsError(.Value) Then
Select Case .Value
Case "Low - Little or no risk presented, continue with control measures in place"
.Font.ColorIndex = 0
.Interior.ColorIndex = 34
Case "Low / Medium - Some risk presented, continue with control measures in place following confirmation from unit manager"
.Font.ColorIndex = 0
.Interior.ColorIndex = 36
Case "Medium - Significant risk presented - Ensure consultation with MDT has occurred prior to activity"
.Font.ColorIndex = 0
.Interior.ColorIndex = 40
Case "High risk presented - Refer to MDT and review control strategies"
.Font.ColorIndex = 0
.Interior.ColorIndex = 3
.Font.Bold = True
End Select
when data is in put in to two cells e.g "G15" & "H15" a calculation is done in a seperate cell "L15" that determines the level of risk e.g. Case "Low - Little or no risk presented, continue with control measures in place" then the private sub will change the format of the cell according to the code (this works perfectly fine). what i want is for the cell to revert back to its orginal state if the two cells are cleared (orginal stating being font colour white and font colorindex being white) this is so that #value is not visible to the user another problem is that the calcution in "L15" is changing numbers into text. if there is no date in "G15" or "H15" "L15" states #value
this is the code is based on this formula =IF(L15<=2,$O$26,IF(L15<=5,$O$27,IF(L15<=8,$O$28,IF(L15>=9,$O$29," "))))
any help would be greatly appreciated, thanks in advance
John
Bookmarks