Programmatically applying conditional formatting to an Excel worksheet

[Halliarse]

Hi

I am using VS2012, VB.Net with the Excel 14.0 library.

I have created an application which compares stock values between an Excel spread-sheet and a table within an SQL database, creating a 'movements' spread-sheet. This all works exactly as I want, however, to help my user view the movements easily, I want to apply conditional formatting to highlight the positive and negative movements....this is where I struggle...a lot!

My movements are in column E and range from row 3 to a variable number of rows dependant on the original spread-sheet. If the movement cell value is greater than zero I want to colour the cell green and conversely, if the movement cell value is less than zero, I want to colour the cell green. I have managed to get cells to change colour but it effects every row and every cell and they are all the same colour so I suspect my issue is I'm not selecting the range or the specific cell correctly!

Could someone please advise what this code should look like?

Many thanks in anticipation!

Steve

 

[Andrzejek]

It’s a little confusing...
"If the movement cell value is [blue]greater than zero[/blue] I want to colour [green]the cell green[/green] and conversely, if the movement cell value [blue]is less than zero[/blue], I want to colour [green]the cell green[/green]."

How about if the value is zero?

And if you would show your code, we could tell you how to fix it.


Have fun.

---- Andy

 

[SkipVought]

hi,

what this code should look like

Have you tried 1) turning on your Macro Recorder and 2) performing the appropriate CF, then 3) turning off the Macro Recorder and 4) observing your recorded code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Halliarse]

Hi Andy

If the movement is less than zero then it needs to be red..typo, sorry! If it is zero then there is no movement so the cell doesn't need highlighting.

My code, such as it is;-

With xlWorkSheet.Range("E3", "E1000")
If .Cells.Value > 0 Then
.Interior.ColorIndex = 4
Else
If .Cells.Value < 0 Then
.Interior.ColorIndex = 3
End If
End If
End With

Thanks

Steve

 

[SkipVought]

Glad you got a solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I would try something like this:

Dim i As Integer

i = 3

Do While Range("E" & i).Value <> ""
    Select Case Range("E" & i).Value
        Case Is > 0
            Range("E" & i).Interior.ColorIndex = 4
        Case Is < 0
            Range("E" & i).Interior.ColorIndex = 3
    End Select
    
    i = i + 1
Loop

Have fun.

---- Andy

 

[Halliarse]

Hi Andy

Thanks for that...I added to my code as below, however I get a 'Conversion from a string to type Double' is not valid on the 'Do' line.

Dim i As Integer

i = 3

Do While xlWorkSheet.Range("E" & i).Value <> ""
Select Case xlWorkSheet.Range("E" & i).Value
Case Is > 0
xlWorkSheet.Range("E" & i).Interior.ColorIndex = 4
Case Is < 0
xlWorkSheet.Range("E" & i).Interior.ColorIndex = 3
End Select

i = i + 1
Loop

Kind regards

Steve

 

[jebenson]

Do While xlWorkSheet.Range("E" & i[red].ToString[/red]).Value <> ""


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Halliarse]

Hi Jebensen

Right idea, wrong place...it needs to be Do While xlWorkSheet.Range("E" & i).Value.ToString <> "" and then it works

Cheers

steve