VBA for Excel 2007 tutorial-highlight all odd rows


VBA code to highlight all odd rows

In this example, We demonstrate the uses of VBA built-in functions Left() and Right() by creating a simple program to highlight rows Excel worksheet. The user can choose to highlight all rows, odd rows, or even rows.

To run this example VBA code, you will need one Form, one TextBox, one ComboBox, one Label, three OptionButtons, and one CommandButton. In addition, you may copy and paste sample data below to your current worksheet.

BookID Title
B001 VBA Programming for Excel 2007
B002 C++ Programming for Beginners
B003 Learn Java in 60 hours
B004 C# Programming for Java Developer
B005 C++ Graphics and Game Programming


VBA for Excel: VBA code example of using VBA buiilt-in functions to highlight rows of Excel Sheet

VBA code in this example:



Option Explicit
Dim lb As Integer 'global declaration
Dim ub As Integer
Private Sub CommandButton1_Click()
    Dim Rng As String       
    If TextBox1.Text <> "" Then   
        Rng = TextBox1.Text 'store the requested range
        lb = CInt(Right(Left(Rng, 2), 1)) 'lower row number
        ub = CInt(Right(Right(Rng, 2), 1)) 'upper row number
       
        If OptionButton1.Value = True Then
         highAll Rng, CInt(ComboBox1.Value) 'highlight all requested rows
        ElseIf OptionButton2.Value = True Then
         highOddRows Rng, CInt(ComboBox1.Value) 'highlight odd rows
        ElseIf OptionButton3.Value = True Then
         highEvenRows Rng, CInt(ComboBox1.Value) 'highlight even rows
        End If
       
    Else: MsgBox "Enter the range"
   
    End If
End Sub
 
Sub highOddRows(Rng As String, CCode As Integer)   
    Do While lb <= ub
        If lb Mod 2 <> 0 Then
            Range(Left(Rng, 1) & lb & ":" & Left(Right(Rng, 2), 1) & lb).Select 'select the odd rows
            With Selection.Interior 'then highlight
            .ColorIndex = CCode
           
             End With
        End If
     lb = lb + 1
    Loop
   
End Sub
 
Sub highAll(Rng As String, CCode As Integer)
    Range(Rng).Select 'select all rows then highlight
    With Selection.Interior
            .ColorIndex = CCode           
    End With
End Sub
 
Sub highEvenRows(Rng As String, CCode As Integer)   
    Do While lb <= ub
        If lb Mod 2 = 0 Then
            Range(Left(Rng, 1) & lb & ":" & Left(Right(Rng, 2), 1) & lb).Select 'select even rows
            With Selection.Interior 'then highlight
            .ColorIndex = CCode
            
             End With
        End If
     lb = lb + 1
    Loop
   
End Sub
 
Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 0 To 10
        ComboBox1.AddItem i 'Store color codes in ComboBox
    Next
    ComboBox1.Value = 0 'default color code
End Sub


Comments

seo plugin comment

 seo plugin

Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword....wait there's even more Now what if i told you there was a simple Wordpress plugin that does all the On-Page SEO, and automatically for you? That's right AUTOMATICALLY, just watch this 4minute video for more information at. <a href="http://www.SEORankingLinks.com">Seo Plugin</a>
seo plugin http://www.SEORankingLinks.com/


2016-09-28




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.