Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

VB in Excel

I'm using Visual Basic in Excel and I have to color every other line in an Excel Worksheet by using Visual Basic.  What line(s) of code to I use to do this?
0
Artemis_23
Asked:
Artemis_23
  • 4
  • 3
1 Solution
 
soosairajCommented:
Dear Friend ,  this will help you to make differentcolor for each line in excel through VB.

Dim App1 As Excel.Application

Set App1=CreateObject"Excel.Application")
        App1.Visible = True
        App1.Workbooks.Add


app1.Rows("2:2").Select 'for second line
   
    With Selection.Interior
        .ColorIndex = 6  'Yellow color
        .Pattern = xlSolid
    End With

app1.Rows("3:3").Select 'for third line
    With Selection.Interior
        .ColorIndex = 53  ' Brown color
        .Pattern = xlSolid
    End With

Thanks

Raj
0
 
calacucciaCommented:
HI Artemis 23,

From your question I understand you're running VBA in Excel. In that case, there is no need to set/call an Object.

Following little sub wil color all your lines with the color red:

Sub ColorLines
Dim Startline As Integer, Endline As Integer
Startline = 1
'To set the last line to line 200
EndLine=200
'To set the last line to be coloured
'To the last used line onyour sheet, use
'the comment on the next line (without the ' of core)
'Endline = Range("A1").SpecialCells(xlCellTypeLastCell).Row
With Rows(Startline & ":" & Endline).Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
End Sub

Good Luck

Calacuccia
0
 
Artemis_23Author Commented:
Okay, the thing is I can color all the rows fine, but i have to color every other row.  How do I do that?

Artemis_23
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
calacucciaCommented:
So you only want to color the even rows.

Here you go:

Sub ColorLines
Dim Startline As Integer, Endline As Integer
'For the odd lines, make Startline equal to 1
Startline = 2
'To set the last line to the last used line
Endline = Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = 2 to Endline Step 2
With Rows(i).Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
Next i
End Sub

Good Luck

Calacuccia
0
 
Artemis_23Author Commented:
If I want to make it rows 6 - 46 coloring every other....how do i do that?

Artemis_23
0
 
calacucciaCommented:
Hi Artemis,

just change the declarations of Startline & Endline to respectively 6 and 46. Like this:

Sub ColorLines
Dim Startline As Integer, Endline As Integer
'For the odd lines, make Startline equal to 1
Startline = 6
'To set the last line to the last used line
Endline = 46
For i = 2 to Endline Step 2
With Rows(i).Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
Next i
End Sub

0
 
Artemis_23Author Commented:
Thanks a lot.  I figured out how to start at 6 and end at 46.  I appreciate your help.  Thanks again.
                          Artemis_23
0
 
calacucciaCommented:
You're walcomen, Artemis.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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