I have a column on an excel spreadsheet that will contain compass degree values 000 to 360. If the value entered is 000 to 045 I want to add 360. Can you help me set the conditional formula/formatting that will do that automatically for those column cells? Thanks Mark
That cannot be done using conditional formatting. If you want to update the value in the cell itself, it requires VBA.
Let's say you want to apply this to C2:C100.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Range("C2:C100"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("C2:C100"), Target)
If rng.Value <> "" Then
If IsNumeric(rng.Value) Then
If rng.Value >= 0 And rng.Value <= 45 Then
rng.Value = rng.Value + 360
Application.EnableEvents = True
Application.ScreenUpdating = True
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Please note that Undo will not work for cells in the specified range.