Highlight cell with VBA in Excel?

Question

I want to write a script that will highlight selected values(Cells) in Excel using VBA editor.

The example is if the value is between 4 -5 or 6-7 or 8-9 then highlight the cell otherwise no action.

The values i use is different but if i can have a scrip then I will modify it to my requirements.

Thanks

Answer 1

Here is a short macro that will highlight the cells in column A that fall into the ranges you specify. If your column is not 'A', change all "A" references to the appropriate column indicator.



Sub HighlightSelected()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Cells(i, "A").Value > 4 And Cells(i, "A").Value < 5 Or _

Cells(i, "A").Value > 6 And Cells(i, "A").Value < 7 Or _

Cells(i, "A").Value > 8 And Cells(i, "A").Value > 9 Then

Cells(i, "A").Interior.ColorIndex = 6

End If

Next

End Sub

Answer 2

1

Answer 3

Cells(myrow, mycol).Select

With Selection

If .Value = lvalue Then

.Font.ColorIndex = 3

.Interior.ColorIndex = 6

Else

.Font.ColorIndex = xlColorIndexAutomatic

.Interior.ColorIndex = xlColorAutomatic

End If

End With



The above is a small part of a VBA Excel macro that I wrote for a stock application. Basically if it finds a particular value in the current cell it highlights the value with .Font.ColorIndex and the background with .Interior.ColorIndex.

Answer 4

You'd be best to use the conditional formatting tool in excel. It can format any cells in a range based on the values in the cell.



Tools > conditional formatting.



You can apply multiple rules to the same cells to assign multiple colours based on a range of values.



It's instantaneous when you type a value in and doesn't require you to run a script or macro.

Answer 5

that's advisable to contemplate a code like that: If ActiveCell.Row = ActiveCell.Column Then Cells(ActiveCell.Row, ActiveCell.Column).indoors.ColorIndex = 37 Else Cells(ActiveCell.Row, ActiveCell.Column).indoors.ColorIndex = xlNone end If

Answer 6

to automate ur process use the following code. u can adjust it to fit in with ur program's purpose

Range("A1:A6").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _

Formula1:="=4", Formula2:="=5"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 13551615

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False