blackpool
12-08-2022, 07:57 PM
When I run it, a box prompts me to pick a cell, and it does it function highlighting the corresponding negative cell. However, I want it to do it for the entire column automatically, skipping blanks.
Sub HighlightNegativeNumbers()
Dim rng As Range
Dim cell As Range
Dim targetCell As Range
Dim targetValue As Double
' Prompt the user to select a cell
Set rng = Application.InputBox("Please select a cell:", Type:=8)
' Get the selected cell's value
targetValue = rng.Value
' Loop through each cell in the worksheet
For Each cell In ActiveSheet.UsedRange
' Check if the cell's value is equal to the negative of the target value
If cell.Value = -targetValue Then
' If the cell's value is equal to the negative of the target value,
' set the cell as the target cell
Set targetCell = cell
Exit For
End If
Next cell
' If a target cell was found, highlight it and the selected cell
If Not targetCell Is Nothing Then
rng.Interior.Color = vbYellow
targetCell.Interior.Color = vbYellow
End If
End Sub
Sub HighlightNegativeNumbers()
Dim rng As Range
Dim cell As Range
Dim targetCell As Range
Dim targetValue As Double
' Prompt the user to select a cell
Set rng = Application.InputBox("Please select a cell:", Type:=8)
' Get the selected cell's value
targetValue = rng.Value
' Loop through each cell in the worksheet
For Each cell In ActiveSheet.UsedRange
' Check if the cell's value is equal to the negative of the target value
If cell.Value = -targetValue Then
' If the cell's value is equal to the negative of the target value,
' set the cell as the target cell
Set targetCell = cell
Exit For
End If
Next cell
' If a target cell was found, highlight it and the selected cell
If Not targetCell Is Nothing Then
rng.Interior.Color = vbYellow
targetCell.Interior.Color = vbYellow
End If
End Sub