Jan 01 2022 11:44 AM
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
Jan 01 2022 12:00 PM
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
End If
End If
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
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.