colouring the entire row to a color .

[roshinpp_77]

Based on conditions i would like to color the entire row permanently.
For eg:

If column C is of value "Y" then i need the entire row's fill color t
be changed to red.
The macro i wrote is not working .Pl ease help

 

[Norman Jones]

Hi Roshinpp,

Try something like:

'================>>
Public Sub Tester()
Dim rng As Range
Dim rcell As Range

Set rng = ActiveSheet.Range("C1:C100") '<<==== CHANGE
For Each rcell In rng.Cells
With rcell
If UCase(.Value) = "Y" Then
.EntireRow.Interior.ColorIndex = 3
End If
End With
Next rcell

End Sub
'<<================



---
Regards,
Norman



"roshinpp_77" <[email protected]>
wrote in message
news:[email protected]...

 

[aidan.heritage]

perhaps if we could see the macro you wrote?

but something like

for each cell in range("C1:C100")
if cell.value="Y" then cell.EntireRow.Interior.ColorIndex = 3
next

would do it for you

 

[roshinpp_77]

Thanks Norman..it was so cool!!! I was struggling for greater
things...Thanks a lot

 

[ChristianH]

Based on conditions i would like to color the entire row permanently.
For eg:

If column C is of value "Y" then i need the entire row's fill color to
be changed to red.
The macro i wrote is not working .Pl ease help.

Hi

This will do it automatically

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell
Set cell = Application.Intersect(Target, Range("C:C"))
If cell Is Nothing Then
Exit Sub
ElseIf cell = "Y" Then
Target.EntireRow.Interior.ColorIndex = 3
End If
End Sub


Cheers Christian

 

[roshinpp_77]

Hi Christian...thanks ...but its not working..i think something i
missed out..nothing is happening to the changes made..pls advice.


Regds,Roshin

 

[ChristianH]

Hi Christian...thanks ...but its not working..i think something is
missed out..nothing is happening to the changes made..pls advice.


Regds,Roshin

Hi

Just tried it and it is working for me

Put it in the worksheet code module and if any cell in column C is
(capital Y )
then the row will change to red.

Christian

 

[David McRitchie]

If you really need permanent change, you would use a
change event macro
http://www.mvps.org/dmcritchie/excel/event.htm
and you would have a companion macro to set the colors
initially. Macros are case sensitive.

But what you describe sounds like you want Conditional Formatting,
except that if you remove the C.F. the changes are not permanent,
they all disappear. (worksheet formulas are not case sensitive)
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Select cell C1 then use Ctrl+A to select all cells (Excel 2002 and prior versions)
so that all cells are selected and cell C1 is the active cell.
Format, Conditional Formatting
Condition 1: Formula is: =$C1="Y"