OnEntry and Change


Using the Worksheet OnEntry property

This method is an alternative to using the Conditional Formatting feature.
The OnEntry event is fired whenever the user enters data in that particular worksheet.
You can use the OnEntry property of either a worksheet or application object.
This event will fire after the user has entered (or modified) the contents of a cell by pressing Enter or by selecting another cell with the mouse.
This event will not fire if the user uses (Edit > Cut) or (Edit > Paste) or if another subroutine changes the contents of any of the cells.


Private Sub Workbook_Open() 
   Sheets("sheetname").OnEntry = "ConditionalFormatting"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
   Sheets("sheetname").OnEntry = ""
End Sub

Any cells on this particular worksheet that have a value greater than 20 will be automatically displayed in bold.

Public Sub ConditionalFormatting() 
   If IsNumeric(ActiveCell.Value) = True Then
      If ActiveCell.Value > 20 Then
         ActiveCell.Font.Bold = True
      Else
         ActiveCell.Font.Bold = False
      End If
   End If
End Sub

Using the Worksheet Change event

In Excel 97 this was replaced with the worksheet Change event and the application SheetChange event.
This subroutine has to appear in the corresponding worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range) 
   If IsNumeric(Target.Value) = True Then
      If Target.Value > 20 Then
         Target.Font.Bold = True
      Else
         Target.Font.Bold = False
      End If
   End If
End Sub

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrev