Change Color of Cell


R

RigasMinho

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
 
Ad

Advertisements

B

Bob Phillips

Add

rngDestination.Interior.Colorindex -3

which colours it red.

--
HTH

Bob Phillips

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

RigasMinho

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?
 
Ad

Advertisements

B

Bob Phillips

rngDestination.Resize(rngFoundAll.Cells.Count).Interior.ColorIndex =
3

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top