Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Cells with Red
I have a spreadsheet that I need to fill the interior color of the row red if
column f contains the text red. I need the data in each row to turn red if f is red for columns a through e and g through m. Is there an easy way to do this other than sorting/filtering. I have already used the conditional formatting option for another purpose. Is there some way to do this with VBA? Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Cells with Red
Sub cb()
n = Cells(Rows.Count, "F").End(xlUp).Row For i = 1 To n If Cells(i, "F").Value = "red" Then Range("A" & i & ":E" & i).Interior.ColorIndex = 3 Range("G" & i & ":M" & i).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student gsnu200708 "CB" wrote: I have a spreadsheet that I need to fill the interior color of the row red if column f contains the text red. I need the data in each row to turn red if f is red for columns a through e and g through m. Is there an easy way to do this other than sorting/filtering. I have already used the conditional formatting option for another purpose. Is there some way to do this with VBA? Any help is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Cells with Red
Thanks Gary. This is just what I needed.
"Gary''s Student" wrote: Sub cb() n = Cells(Rows.Count, "F").End(xlUp).Row For i = 1 To n If Cells(i, "F").Value = "red" Then Range("A" & i & ":E" & i).Interior.ColorIndex = 3 Range("G" & i & ":M" & i).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student gsnu200708 "CB" wrote: I have a spreadsheet that I need to fill the interior color of the row red if column f contains the text red. I need the data in each row to turn red if f is red for columns a through e and g through m. Is there an easy way to do this other than sorting/filtering. I have already used the conditional formatting option for another purpose. Is there some way to do this with VBA? Any help is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Cells with Red
One way:
Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rIntersect As Range Dim rCell As Range Dim nCI As Long Set rIntersect = Intersect(Target, Columns("F")) If Not rIntersect Is Nothing Then For Each rCell In rIntersect With rCell If LCase(rCell.Text) = "red" Then nCI = 3 Else nCI = xlColorIndexNone End If Union(.Offset(0, -5).Resize(1, 5), _ .Offset(0, 1).Resize(1, 7)).Interior.ColorIndex = nCI End With Next rCell End If End Sub In article , CB wrote: I have a spreadsheet that I need to fill the interior color of the row red if column f contains the text red. I need the data in each row to turn red if f is red for columns a through e and g through m. Is there an easy way to do this other than sorting/filtering. I have already used the conditional formatting option for another purpose. Is there some way to do this with VBA? Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill Cells Based On Cells In Another Sheet | Excel Worksheet Functions | |||
Fill cells with color based on criteria in two cells | Excel Worksheet Functions | |||
How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
create a fill in template to tab to fill in cells | Excel Discussion (Misc queries) | |||
HOW TO FORMATE CELLS TO COUNT CELLS WITH A FILL COLOR? | New Users to Excel |