...if instead of colouring the cell, can the result be shown within a circle in that cell in any way... i.e. the cell should be automatically circled if the result goes above 3.
Yes this is possible.
Three important points:
Important item #1, the below code assumes you accurately protrayed your data's layout,
especially, that the range you care about for numbers greater than 3 is from F21 to F of the last used row.
Important item #2, the below code also assumes the cells in range F21 to F [whatever]
contain formulas.
Important item #3, be sure you install the below code by right clicking on the sheet where all this fun stuff is going on, then left click on View Code, and paste the below code into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
To get the code to start working,
calculate the sheet or enter some value in some cell that generates another cell's formula to recalculate, and you will see circles around cells in column F that have numbers in them greater than 3.
The code:
Code:
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim cell As Range, CircleRange As Range
Dim oldShape As Shape, newShape As Shape
Dim iCount%
iCount = 0
Set CircleRange = Range("F21:F" & Cells(Rows.count, 6).End(xlUp).Row)
For Each oldShape In ActiveSheet.Shapes
If oldShape.Name Like "GreaterThanThree_*" Then oldShape.Delete
Next
For Each cell In CircleRange
With cell
If .Value > 3 Then
Set newShape = ActiveSheet.Shapes.AddShape(msoShapeOval, .Left - 2, .Top - 2, .Width + 4, .Height + 4)
newShape.Fill.Visible = msoFalse
newShape.Line.ForeColor.SchemeColor = 10
newShape.Line.Weight = 1.25
iCount = iCount + 1
newShape.Name = "GreaterThanThree_" & iCount
End If
End With
Next cell
Set newShape = Nothing
Set CircleRange = Nothing
Application.ScreenUpdating = True
End Sub
