Change Color of Cell
 

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range
Code below finds a column with value A and pastes it in worksheet
output. How do I change the color of the cell when it pastes the
output?

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("B")
Set rngFound = rngToSearch.Find(What:="A", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -1).Copy rngDestination

Change Color of Cell
 

Add

rngDestination.Interior.Colorindex -3

which colours it red.

--
HTH

Bob Phillips

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

"RigasMinho" wrote in message
ups.com...
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range
Code below finds a column with value A and pastes it in worksheet
output. How do I change the color of the cell when it pastes the
output?

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("B")
Set rngFound = rngToSearch.Find(What:="A", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -1).Copy rngDestination

Change Color of Cell
 

Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -1).Copy rngDestination
rngDestination.Interior.ColorIndex = 3

only changes the first cell - i want it so that it changes all the
cells in the out.

Anyone?
Bob Phillips wrote:
Add

rngDestination.Interior.Colorindex -3

which colours it red.

--
HTH

Bob Phillips

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

"RigasMinho" wrote in message
ups.com...
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range
Code below finds a column with value A and pastes it in worksheet
output. How do I change the color of the cell when it pastes the
output?

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("B")
Set rngFound = rngToSearch.Find(What:="A", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -1).Copy rngDestination

Change Color of Cell
 

rngDestination.Resize(rngFoundAll.Cells.Count).Int erior.ColorIndex =
3

--
HTH

Bob Phillips

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

"RigasMinho" wrote in message
oups.com...

Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -1).Copy rngDestination
rngDestination.Interior.ColorIndex = 3

only changes the first cell - i want it so that it changes all the
cells in the out.

Anyone?
Bob Phillips wrote:
Add

rngDestination.Interior.Colorindex -3

which colours it red.

--
HTH

Bob Phillips

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

"RigasMinho" wrote in message
ups.com...
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range
Code below finds a column with value A and pastes it in worksheet
output. How do I change the color of the cell when it pastes the
output?

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("B")
Set rngFound = rngToSearch.Find(What:="A", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, -1).Copy rngDestination