Coloring of Rows

[scorpion53061]

I want to color the row black and set the font white in an excel spreadsheet
if:

the TOTAL column is > 1

and

more than one of the other columns are > 0. (In other words a minimum of two
columns in the spreadsheet are greater than 0)

Also those of you familiar with ADO.NET can you suggest a resource to learn
how to do charts in excel based on ADO.NET dataset data?

Here is where I am at....

Dim ObjVal As Object
ObjVal = WSheet.Cells(p + 2, 26).Value
Dim days As Integer
days = ObjVal
(Days is the value of the TOTAL cell and I need to put something here to
find the more than one column value to tell it to color the row - not sure
how to acomplish the font thing)
If days > 1 Then
With WSheet.Rows.Item(p, 1).Interior
.Pattern = 1 'xlSolid

End With
End If

 

[Tom Ogilvy]

Look at format=>conditional formatting to color your row.

 

[Bob Phillips]

Do you want

With WSheet.Rows.Item(p, 1).Entirerow.Interior
.ColorIndex = 1
.Pattern = 1 'xlSolid

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[scorpion53061]

HI guys I should have waited for your response. THis group has always been
good about responding.....

This code gives me a "range error" but is not being more specific......any
ideas? If the logic is wrong let me know too okay

Thank you much

If WSheet.Cells(1, 26).Value = "TOTALQTY" Then
WSheet.Columns("A").Select()
Dim p As Integer
For p = 2 To listds.Tables(0).Rows.Count - 1
Dim ObjVal As Object
ObjVal = WSheet.Cells(p + 2, 26).Value
Dim days As Integer
days = ObjVal
If days > 1 Then
Dim q As Integer
Dim columnshigherthanzero As Integer
Dim objVal2 As Object
For q = 1 To listds.Tables(0).Columns.Count - 1 'columns
in spreadsheet
objVal2 = WSheet.Cells(p + 2, q)
If objVal2 > 1 Then
columnshigherthanzero = columnshigherthanzero +
1
End If
If columnshigherthanzero >= 2 Then
WSheet.Rows(p, 1).Select()
With WSheet.Application.Selection
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = 2
End With
With WSheet.Application.Selection.Interior
.ColorIndex = 1
.Pattern = 1
'.PatternColorIndex = 'xlAutomatic
End With
End If
Next
End If
Next
End If

 

[Tom Ogilvy]

Where is the error?

what is this?

listds.Tables(0).Rows.Count

What is Listds
What is Tables(0)

why do you declare objval as an object and then assign a string or number to
it?

--
Regards,
Tom Ogilvy

 

[scorpion53061]

Hi Tom,

No actual error is occuring now for some reason. However the color of the
rows is not showing either.

what is this?

listds.Tables(0).Rows.Count

The number of the rows going to be used in the spreadsheet. This is a
ADO.NET dataset that is zero based thus the funkying numbering you see.

What is Listds
What is Tables(0)

listds is the name fo the dataset holding the data that the excel
spreadsheet is getting.

(See http://www.kjmsolutions.com/datasetarray.htm for a full explanation)

Tables(0) is specifying what table to use in the dataset. vb.net unlike vba
requires a default member to be specified.

why do you declare objval as an object and then assign a string or number to
it?

I believe at one point I had had an error so I used a generic object value.
You are right though. I will switch it back.

 

[scorpion53061]

Tom,

This is the version not giving me any error but is not coloring the row that
has more than 2 columns and the TOTAL column greater than zero.

If WSheet.Cells(1, 26).Value = "TOTALQTY" Then
WSheet.Columns("A").Select()
Dim p As Integer
For p = 2 To listds.Tables(0).Rows.Count - 1
Dim ObjVal As Object
ObjVal = WSheet.Cells(p + 2, 26).Value
Dim days As Integer
days = ObjVal
If days > 1 Then
Dim q As Integer
Dim columnshigherthanzero As Integer
Dim objVal2 As Integer
For q = 1 To listds.Tables(0).Columns.Count - 1 'columns
in spreadsheet
objVal2 = WSheet.Cells(p + 2, q + 1).Value
If objVal2 > 1 Then
columnshigherthanzero = columnshigherthanzero +
1
End If
If columnshigherthanzero >= 2 Then
WSheet.Rows(p + 2, 1).Select()
With WSheet.Application.Selection
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = 2
End With
With WSheet.Application.Selection.Interior
.ColorIndex = 1
.Pattern = 1
.PatternColorIndex = -4105 'xlAutomatic
End With
End If
q = q + 1
Next
End If
Next
End If

 

[scorpion53061]

Tom,

I appear to have resolved this issue.

If you would like me to post the solution please let me know.