PDA

View Full Version : entire row



oleg_v
05-23-2010, 11:13 PM
hi
how can i change
in this line: Selection.EntireRow.Interior.ColorIndex = f
instead of "EntireRow" color between colums "b" and "s"


thanks

Aussiebear
05-23-2010, 11:38 PM
Not knowing which row the "EntireRow" refers to because you supplied not enough of the code to read from

Try
Selection.Range("B1:S1").Interior.ColorIndex = f

oleg_v
05-24-2010, 12:06 AM
hi
i attached the code what i need is to color the rows only between columns "b"and"c"

Sub SAME()
Dim Rng As Range
Dim cel As Range
Dim tgt As Range
Dim cl As String
k = 0
Dim i As Long, y As Long
y = Sheets("sheet1").Range("r65536").End(xlUp).row
Set tgt = Sheets("sheet2").Range("a3")
Set Rng = Range("r7:q" & y)
For Each cel In Rng
If Application.CountIf(Rng, cel) = 2 Then
cel.EntireRow.Copy

k = k + 1
tgt.Offset(i).PasteSpecial
If k = 57 Then
GoTo k
End If
i = i + 1
End If
Next
k:
Sheets("sheet2").Select
Dim Found As Range, It
Dim h As Long, f

h = 2
f = 2

one:
h = h + 1
f = f + 1
If f > 56 Then ' 56 colors excel have

Exit Sub
End If

With Sheets("sheet2").Rows(3)

lastcol = Cells(.row, Columns.Count).End(xlToLeft).Column
End With

cl = Left$(Columns(lastcol).Address(True, False), InStr(Columns(lastcol).Address(True, False), ":") - 1)
y = Sheets("sheet2").Range("r65536").End(xlUp).row
'MsgBox cl
myvar = Worksheets("Sheet2").Range("r" & h).Value

It = myvar 'InputBox("Enter search term")
Set Found = Columns("r").Find(What:=It, LookIn:=xlValues, LookAt:=xlWhole)
If Not Found Is Nothing Then
Range(Found.Address(False, False)).Select
'MsgBox lastcol
'MsgBox h
RSelection.EntireRow.Interior.ColorIndex = f


Cells.FindNext(After:=ActiveCell).Activate
Selection.EntireRow.Interior.ColorIndex = f
'MsgBox It & " found in " & Found.Address(False, False), vbInformation
Else
' MsgBox It & " not found.", vbExclamation
End If

Sheets("sheet2").Range("ab30") = f



If h < y Then
GoTo one
Else
Exit Sub
End If

End Sub

mdmackillop
05-24-2010, 04:46 AM
Look at the Offset and Resize functions, or define the first cell using another variable's properties. Whichever is appropriate


Found.Offset(,-10).Resize(,18)
'or
Cells(Found.Row,2).Resize(,18)