Can anyone help with VB please?

[kirkm]

Hi,

I'm way out of my depth and been struggling with this day.

There's probably a million things wrong... I want to step through a
range of cells and print the font name and colour. Eventually I want
to change the colour depending on the number. I can see the cell
contents but everything else is just 'Null'.

It's these 'dot' bits that I don't follow at all. Any help
appreciated.

Thanks - Kirk

-----
Sub ReadColours()

Dim wk As Workbook
Set wk = ActiveWorkbook
Dim sht As Worksheet
Set sht = wk.Worksheets("Sheet1")

Dim c

With sht
For Each c In sht.Range("S2:CD5270")

Debug.Print c
With .Cells.Font
Debug.Print .Name
End With
With .Cells.Interior
Debug.Print .ColorIndex
End With

Next
End With

End Sub

 

[moon]

Nopes, not a million things, you're almost there.


Sub ReadColours()
Dim wk As Workbook
Set wk = ActiveWorkbook
Dim sht As Worksheet
Set sht = wk.Worksheets("Sheet1")
Dim c As Variant
With sht
For Each c In sht.Range("S2:CD5270")
Debug.Print c.Address
With c.Font
Debug.Print .Name
Debug.Print .ColorIndex & vbCrLf
End With
Next
End With
Set sht = Nothing
Set wk = Nothing
End Sub

 

[moon]

So c does not have the .Cell property.
c is the cell.
That's why it produced the null values

 

[Bob Phillips]

When you do the

With .Cells.Font

you have identified any parent object, so you code should be bombing out at
that point. You need to qualify more

With sht
For Each c In .Range("S2:CD5270")
Debug.Print c
Debug.Print c.Font.Name
Debug.Print c.Interior.ColorIndex
Next
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kirkm]

So c does not have the .Cell property.
c is the cell.
That's why it produced the null values

Many Thanks moon, it worked a treat and allowed the
colour changes I was hoping for.

One day I might get all this....

This group is brilliant

Cheers - Kirk