stuartgb100
03-09-2017, 11:53 PM
Hi,
I have a function which checks for fill in column K.
The cells to be checked are defined by rng.
There is red fill in K1999.
The function is called by the Worksheet_Change event.
If I click on a cell in col D and enter a value, the function runs
and the value in K2000 becomes ERROR
If I click on another cell in col D and enter a value, the function
runs and K2000 becomes 0.00 as a result of a sum formula.
The red fill is still in K1999.
I keep entering values in col D and the results keep alternating,
first Error then 0.00.
Here is the code:
Function CheckForFill()
Dim rng As Range
'Assumes the correct row number for TotalCell is in E1.
'Uses E1 to set the user data range.
'Checks for a red cell in col K within the data range.
'If found, TotalCell is marked "ERROR".
'If no red cell, then it puts the sumformula in TotalCell.
CheckForFill = False
Set rng = Range("K4", ("K") & Range("E1").Value - 1)
If ActiveSheet.AutoFilterMode = True Then
rng.AutoFilter
rng.AutoFilter Field:=1, Operator:=xlFilterNoFill
End If
If Not rng.SpecialCells(xlCellTypeVisible).Count = rng.Count Then
CheckForFill = False 'found a fill cell(s)
With Range("K" & Range("E1").Value)
.Value = "ERROR"
.Interior.Color = 16777215
.Interior.ColorIndex = 0
End With
rng.AutoFilter
Exit Function
Else
CheckForFill = True 'no 'fill' cells
Range("K" & Range("E1").Value) = Application.Sum(rng)
Range("K" & Range("E1")).Interior.ColorIndex = 0
End If
rng.AutoFilter
Range("A1:K2").Locked = True
Range("A" & Range("E1").Value, "K" & Range("E1").Value).Locked = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Function
Thanks.
I have a function which checks for fill in column K.
The cells to be checked are defined by rng.
There is red fill in K1999.
The function is called by the Worksheet_Change event.
If I click on a cell in col D and enter a value, the function runs
and the value in K2000 becomes ERROR
If I click on another cell in col D and enter a value, the function
runs and K2000 becomes 0.00 as a result of a sum formula.
The red fill is still in K1999.
I keep entering values in col D and the results keep alternating,
first Error then 0.00.
Here is the code:
Function CheckForFill()
Dim rng As Range
'Assumes the correct row number for TotalCell is in E1.
'Uses E1 to set the user data range.
'Checks for a red cell in col K within the data range.
'If found, TotalCell is marked "ERROR".
'If no red cell, then it puts the sumformula in TotalCell.
CheckForFill = False
Set rng = Range("K4", ("K") & Range("E1").Value - 1)
If ActiveSheet.AutoFilterMode = True Then
rng.AutoFilter
rng.AutoFilter Field:=1, Operator:=xlFilterNoFill
End If
If Not rng.SpecialCells(xlCellTypeVisible).Count = rng.Count Then
CheckForFill = False 'found a fill cell(s)
With Range("K" & Range("E1").Value)
.Value = "ERROR"
.Interior.Color = 16777215
.Interior.ColorIndex = 0
End With
rng.AutoFilter
Exit Function
Else
CheckForFill = True 'no 'fill' cells
Range("K" & Range("E1").Value) = Application.Sum(rng)
Range("K" & Range("E1")).Interior.ColorIndex = 0
End If
rng.AutoFilter
Range("A1:K2").Locked = True
Range("A" & Range("E1").Value, "K" & Range("E1").Value).Locked = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Function
Thanks.