Formatting Excel Cells from VB 1

[SteveGlo]

I am working on dumping the contents of a flexigrid to an Excel spreadsheet and I want to change the colour of some of the cells.

I have some crude code that works after a fashion:

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add(xlWBATWorksheet)
Set xlSheet = xlBook.Worksheets.Add

For c = 0 To icols
For r = 0 To irows

FlexDatesDays.Row = r
FlexDatesDays.Col = c

strtext = FlexDatesDays.Text

If r = 0 Then
If IsDate(strtext) Then
strtext = Format(strtext, "dd-mmm-yyyy")
End If
End If

If Trim$(strtext) = "" Then strtext = "XXXX"

xlSheet.Cells((r + 1), (c + 1)) = strtext

If FlexDatesDays.CellBackColor = vbGreen Then
xlSheet.Cells((r + 1), (c + 1)).Font.Color = vbGreen
ElseIf FlexDatesDays.CellBackColor = vbRed Then
xlSheet.Cells((r + 1), (c + 1)).Font.Color = vbRed
Else
If r > 0 And c > 0 Then
xlSheet.Cells((r + 1), (c + 1)).Font.Color = vbWhite
End If
End If

Next r
Next c


The problem I have is with the line:

If Trim$(strtext) = "" Then strtext = "XXXX"

I don't really want to populate the cells with "XXX" at all.

In the flexi grid the cells are red or blue depending on various criteria but they don't contain any text just the colour shows up which is all that is wanted.

However if I try to change the colour of the Excel cell without there being any text in the cell no colour is shown.

Is it possible to change the cell colour in the spreadsheet giving the same affect if you go into the spreadsheet and select Format Cells, Patterns and then choose a colou for the cell?

 

[SkipVought]

Hi,

Instead of .Font, use .Interior.color or .interior.colorindex on the range object.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[SteveGlo]

skip

I'll try what you suggest and see what happens. Thanks for the prompt reply.

After I posted I remembered that if you start a macro in Excel you can examine the code - so I did that and got

xlSheet.Range("G1", "G7").Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

which is part of the way there - I'll have to fiddle about with variables instead of hardcoding the cell references but I think I can see some light now.

steve

 

[SkipVought]

Something like

with xlSheet.Range("G1", "G7")
  with .font
    .bold = true
    .color = vbred
  end with
  with .interior
    .colorindex = n
  ......
  end with
end with

you do not need to use the Select method -- slows things down!


Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[SteveGlo]

skip

this code

xlSheet.Cells(10, 10).Interior.Color = vbGreen

does exactly what I want outside the loops but not when I use it inside the loops using the r and c variables - so I have a little more work to do.

But you have answered my question so thanks a lot and have a star.

Dteve

 

[TipGiver]

SteveGlo,

Push the button to record a macro at the excel. Press Alt-F11 to open the visual basic editor. Take a look at the generated code inside the macro(). You can then copy and paste the code into the vb application. Perhaps some small changed would be needed, so as to make it work as visual basic code.

 

[SteveGlo]

TipGiver

Thanks - I remembered that after I posted .

Thanks to Skip's advice on using interior instead of font the code now does what I want.

For c = 0 To icols
For r = 0 To irows

FlexDatesDays.Row = r
FlexDatesDays.Col = c
rplus = r + 1
cplus = c + 1

strtext = FlexDatesDays.Text

If r = 0 Then
If IsDate(strtext) Then
strtext = Format(strtext, "dd-mmm-yyyy")
End If
End If

xlSheet.Cells(rplus, cplus) = strtext

If FlexDatesDays.CellBackColor = vbRed Then
xlSheet.Cells(rplus, cplus).Interior.Color = vbRed
End If
Next r
Next c