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

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



"kirkm" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> 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

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




"moon" <(E-Mail Removed)> schreef in bericht
news:45274f80$0$2020$(E-Mail Removed)...
>
> 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
>
>
>
> "kirkm" <(E-Mail Removed)> schreef in bericht
> news:(E-Mail Removed)...
>> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

On Sat, 7 Oct 2006 09:04:24 +0200, "moon"
<(E-Mail Removed)> wrote:

>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