Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Cell Change Color - Need Help | New Users to Excel | |||
Cell references change when entering new data | New Users to Excel | |||
Look for change next blank cell in Range | Excel Worksheet Functions |