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 to 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]...
>
> 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.
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile:
> http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276
>

[[email protected]]

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 wrote:
> 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.
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276

[roshinpp_77]

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

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 wrote:
> 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.
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276

[ChristianH]

roshinpp_77 wrote:

> 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.
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276


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 is missed out..nothing is happening to the changes made..pls advice.


Regds,Roshin

roshinpp_77 wrote:

> 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.
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276


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

[ChristianH]

roshinpp_77 wrote:

> Hi Christian...thanks ...but its not working..i think something is
> missed out..nothing is happening to the changes made..pls advice.
>
>
> Regds,Roshin
>
>
> ChristianH Wrote:
> > roshinpp_77 wrote:
> >
> > > 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.
> > >
> > >
> > > --
> > > roshinpp_77
> > >
> > ------------------------------------------------------------------------
> > > roshinpp_77's Profile:
> > http://www.excelforum.com/member.php...o&userid=34924
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=550276
> >
> >
> > 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
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276


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"

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"roshinpp_77" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> roshinpp_77
> ------------------------------------------------------------------------
> roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
> View this thread: http://www.excelforum.com/showthread...hreadid=550276
>