July 7, 2015

Color Highlight Data Using Excel VBA

Nowadays people wish to participate in equity markets to improve their long-term earnings and plan their taxes. One of the participants in our Excel VBA training program had a question about investing in certain bank stocks and he had a good strategy. He had studied the balance sheet, profit and loss statement, cash flow statement, earnings per share (EPS) growth and had also calculated the price to earning per share ratio (P/E). He wanted to now have a quick and easy way to highlight those companies (banks) in which he could make a good return. He had the data of the intra-day, weekly, quarterly, yearly, 3-yearly and 5-yearly returns. Watch the video below to see how a simple and efficient looping process can help him make a decision:

 

Watch the video on YouTube.

Below is the complete Excel VBA code:

Sub highlightValues()
Dim ws As Worksheet
Dim i As Long, lastrow As Long
Set ws = Worksheets(“Sheet1”)
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To lastrow
If Cells(i, 7).Value > 20 And Cells(i, 8).Value > 20 And Cells(i, 9).Value > 20 Then
‘http://www.rapidtables.com/web/color/RGB_Color.htm
Cells(i, 7).Interior.Color = RGB(102, 255, 102)
Else
Cells(i, 7).Interior.Color = xlNone
End If
Next i

End Sub

Of course, we can tweak the code to highlight other data.

Further reading:

For Next Loop

One thought on “Color Highlight Data Using Excel VBA

  1. Hi Mr. Kumar,
    I’m new to Vba and I trying to write a macro that will find the row that contains (“BOE (Gas 6:1 ratio)”) and highlight the entire row.
    can you please advise? or provide a sample code?
    Best regards,
    AlexD

Comments are closed.