early beginner not satisfied with simple macro
 

Hi,
Although this macro works, is there a way to improve it ?

===start===

Option Explicit

Sub FormatHighestValue()
Dim c as Rang
Dim iEndRow as Integer
Dim iEndCol as String
iEndCol = ThisWorkbook.Sheets(1).Range("B2").End(xlToRight). Address
iEndRow = ThisWorbook.Sheets(1).Range("B2").End(xlDown).Row
iEndCol = Left(iEndCol, 2)
iEndCol = Right (iEndCol, 1)
ThisWorkbook.Range("B2" & ":" & iEndCol & iEndRow).Select
For Each c in Selection.Cells
If c.Value 1000 Then
c.Font.ColorIndex = 3
End If
next c
End Sub

===end===

I think code about iEndCol could be reduced...

Thanks in advance for your help.

Ppp

early beginner not satisfied with simple macro
 

There were a fair few errors in there, but the principle was okay

Sub FormatHighestValue()
Dim c As Range
Dim iEndRow As Integer
Dim iEndCol As String
With ThisWorkbook.Sheets(1)
iEndCol = .Range("B2").End(xlToRight).Column
iEndRow = .Range("B2").End(xlDown).Row
iEndCol = Left(iEndCol, 2)
iEndCol = Right(iEndCol, 1)
For Each c In .Range("B2" & ":", Cells(iEndRow, iEndCol))
If c.Value 1000 Then
c.Font.ColorIndex = 3
End If
Next c
End With
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"wbrowse" wrote in message
ups.com...
Hi,
Although this macro works, is there a way to improve it ?

===start===

Option Explicit

Sub FormatHighestValue()
Dim c as Rang
Dim iEndRow as Integer
Dim iEndCol as String
iEndCol = ThisWorkbook.Sheets(1).Range("B2").End(xlToRight). Address
iEndRow = ThisWorbook.Sheets(1).Range("B2").End(xlDown).Row
iEndCol = Left(iEndCol, 2)
iEndCol = Right (iEndCol, 1)
ThisWorkbook.Range("B2" & ":" & iEndCol & iEndRow).Select
For Each c in Selection.Cells
If c.Value 1000 Then
c.Font.ColorIndex = 3
End If
next c
End Sub

===end===

I think code about iEndCol could be reduced...

Thanks in advance for your help.

Ppp

early beginner not satisfied with simple macro
 

oki, thx