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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *