Code only doing it for cell not entire column

[blackpool]

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

[Grade4.2]

Here is a revised version of the code that will automatically highlight the negative cells in the entire column without prompting the user to select a cell:



Sub HighlightNegativeNumbers()
Dim rng As Range
Dim cell As Range
Dim targetValue As Double
' Set the range to the entire column of the selected cell
Set rng = Selection.EntireColumn
' Loop through each cell in the range
For Each cell In rng.Cells
' Check if the cell's value is negative
If cell.Value < 0 Then
' If the cell's value is negative, highlight it
cell.Interior.Color = vbYellow
End If
Next cell
End Sub


Note: This code assumes that the user has selected a cell in the column before running the macro.

[Grade4.2]

To skip the prompt and automatically highlight negative numbers in the entire column, you can modify the code as follows:


Sub HighlightNegativeNumbers()
Dim cell As Range
Dim targetCell As Range
Dim targetValue As Double
' Loop through each cell in the used range of the active sheet
For Each cell In ActiveSheet.UsedRange
' Check if the cell's value is negative
If cell.Value < 0 Then
' If the cell's value is negative, set it as the target cell
Set targetCell = cell
' Highlight the target cell
targetCell.Interior.Color = vbYellow
End If
Next cell
End Sub

This code will loop through each cell in the used range of the active sheet, check if its value is negative, and if it is, it will highlight it.

Note that this code assumes that the cells in the used range of the active sheet only contain numeric values, otherwise an error will occur. You can add a check to avoid this by adding an IsNumeric condition before checking for the cell's value, like this:


Sub HighlightNegativeNumbers()
Dim cell As Range
Dim targetCell As Range
Dim targetValue As Double
' Loop through each cell in the used range of the active sheet
For Each cell In ActiveSheet.UsedRange
' Check if the cell's value is numeric
If IsNumeric(cell.Value) Then
' Check if the cell's value is negative
If cell.Value < 0 Then
' If the cell's value is negative, set it as the target cell
Set targetCell = cell
' Highlight the target cell
targetCell.Interior.Color = vbYellow
End If
End If
Next cell
End Sub