• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Highlighting in colour Rows of information in Excel using Visual Basic

Hi
I would like to look at some financial data and highlight in colour rows of interest. The data would take the form below. I would use be using something like

If myOpen - 20 <= myLow And myHigh - 20 <= myClose then hightlight the  whole row

Or any other variation that crops up.


10/13/2003      9676      9783      9676      9764
10/14/2003      9763      9813      9732      9813
10/15/2003      9824      9850      9764      9803
10/16/2003      9799      9823      9730      9792
10/17/2003      9792      9816      9702      9722
10/20/2003      9722      9778      9706      9778
10/21/2003      9778      9784      9737      9748
10/22/2003      9742      9742      9580      9598
10/23/2003      9597      9622      9552      9613
10/24/2003      9601      9601      9498      9582
10/27/2003      9585      9664      9585      9608
10/28/2003      9610      9750      9610      9748
10/29/2003      9747      9787      9725      9775
10/30/2003      9772      9839      9754      9787
10/31/2003      9787      9840      9787      9801
11/03/2003      9802      9896      9802      9858

Hope this is enough info and enough points as I just want to get on with this!

Irazor


0
Irazor
Asked:
Irazor
  • 3
  • 3
1 Solution
 
ChristophMurczekCommented:
Hi,
how do you want to output this? You could use for example an MSFLexgrid-Control in a form. There you can set the background color for a row like this:

myFlexgrid.Col=0
myFlexgrid.Colsel=4
myFlexgrid.CellBackColor=vbGreen

hope this helps,
Christoph
0
 
IrazorAuthor Commented:
For the time being I would highlight the rows on the Spreadsheet.

Irazor
0
 
ChristophMurczekCommented:
Hi again,
I just saw that you're working with Excel. Sorry 'bout that. it works like follows:

dim myWS as Excel.Worksheet

myWS.Range(Cell1,Cell2).Interior.Color = vbGreen

Cheers,
Christoph
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
IrazorAuthor Commented:
Perhaps I should say that I only want to hightlight the 5 cells in the Row.
0
 
EDDYKTCommented:
Range("A1:E1").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
0
 
ChristophMurczekCommented:
OK. Try this:

myWS.Rows(Rowindex).Select

Christoph
0
 
IrazorAuthor Commented:
This is what I actually meant, something like this. Thanks for your help anyway.

Sub myanother()

  For i = 3 To 1483
         myOpen = Cells(i, 2).Value
         myHigh = Cells(i, 3).Value
         myLow = Cells(i, 4).Value
         myClose = Cells(i, 5).Value
 
       If myOpen - 20 < myLow And myHigh - 20 < myClose Then
       
           For cols = 1 To 6
                Cells(i, cols).Interior.ColorIndex = 47
          Next cols
     
       End If
 
  Next i
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now