This is a repeat post from yesterday which I'm still struggling with. Gary"s
Student offered some help but:
Sheet uses columns "A-AD", rows 4-10000.
Several sets of adjacent columns are shaded (visually grouped together) to
give clarity for the user.
Here's the premise:
Using a formula in column "AB", either "W", "SD", "ST" or "" will be returned.

I need code to look at column "AB" and colour the row (from "A:AD" only)
red, orange or blue for the first 3 options or leave as is for "".
This part is straightforward ChangeEvent code. However, and this is the bit
I can't fathom, if a cell in "AB" is changed from any of the 3 options back
to blank, the original cell shading needs to be re-applied.
Row 3 is the header row for all the columns, so could the fill colour from
that row be used in the code to correctly re-shade the changed row?
I can email worksheet if req'd.
Thanks
Traa Dy Liooar

Jock

use conditional formatting
select columnds D:AD
set the 'cell value is' to 'formula is' and then the formula to

=$AD1-"W" and then set a pattern color
add two more conditions like this for the other two letter patterns

conditional formatting allows three tests, so you got lucky!



"Jock" wrote:

> This is a repeat post from yesterday which I'm still struggling with. Gary"s
> Student offered some help but:
> Sheet uses columns "A-AD", rows 4-10000.
> Several sets of adjacent columns are shaded (visually grouped together) to
> give clarity for the user.
> Here's the premise:
> Using a formula in column "AB", either "W", "SD", "ST" or "" will be returned.
>
> I need code to look at column "AB" and colour the row (from "A:AD" only)
> red, orange or blue for the first 3 options or leave as is for "".
> This part is straightforward ChangeEvent code. However, and this is the bit
> I can't fathom, if a cell in "AB" is changed from any of the 3 options back
> to blank, the original cell shading needs to be re-applied.
> Row 3 is the header row for all the columns, so could the fill colour from
> that row be used in the code to correctly re-shade the changed row?
> I can email worksheet if req'd.
> Thanks
> Traa Dy Liooar
>
> Jock

use conditional formatting
select columnds D:AD
set the 'cell value is' to 'formula is' and then the formula to

=$AD1-"W" and then set a pattern color
add two more conditions like this for the other two letter patterns

conditional formatting allows three tests, so you got lucky!


"Jock" wrote:

> This is a repeat post from yesterday which I'm still struggling with. Gary"s
> Student offered some help but:
> Sheet uses columns "A-AD", rows 4-10000.
> Several sets of adjacent columns are shaded (visually grouped together) to
> give clarity for the user.
> Here's the premise:
> Using a formula in column "AB", either "W", "SD", "ST" or "" will be returned.
>
> I need code to look at column "AB" and colour the row (from "A:AD" only)
> red, orange or blue for the first 3 options or leave as is for "".
> This part is straightforward ChangeEvent code. However, and this is the bit
> I can't fathom, if a cell in "AB" is changed from any of the 3 options back
> to blank, the original cell shading needs to be re-applied.
> Row 3 is the header row for all the columns, so could the fill colour from
> that row be used in the code to correctly re-shade the changed row?
> I can email worksheet if req'd.
> Thanks
> Traa Dy Liooar
>
> Jock

Ideally, yes; if only it were that simple.
I've used CF on quite a few columns already to flag when a date is out of
tolerence - hence the request for code.
Thanks though, Patrick
--
Traa Dy Liooar

Jock


"Patrick Molloy" wrote:

> use conditional formatting
> select columnds D:AD
> set the 'cell value is' to 'formula is' and then the formula to
>
> =$AD1-"W" and then set a pattern color
> add two more conditions like this for the other two letter patterns
>
> conditional formatting allows three tests, so you got lucky!
>
>
>
> "Jock" wrote:
>
> > This is a repeat post from yesterday which I'm still struggling with. Gary"s
> > Student offered some help but:
> > Sheet uses columns "A-AD", rows 4-10000.
> > Several sets of adjacent columns are shaded (visually grouped together) to
> > give clarity for the user.
> > Here's the premise:
> > Using a formula in column "AB", either "W", "SD", "ST" or "" will be returned.
> >
> > I need code to look at column "AB" and colour the row (from "A:AD" only)
> > red, orange or blue for the first 3 options or leave as is for "".
> > This part is straightforward ChangeEvent code. However, and this is the bit
> > I can't fathom, if a cell in "AB" is changed from any of the 3 options back
> > to blank, the original cell shading needs to be re-applied.
> > Row 3 is the header row for all the columns, so could the fill colour from
> > that row be used in the code to correctly re-shade the changed row?
> > I can email worksheet if req'd.
> > Thanks
> > Traa Dy Liooar
> >
> > Jock

How did the shading in columns A:AD get into the cells... conditional
formatting or manual placed? If manually placed, is there some common item
that can be used to identify these colored cells (type of values they
contain, certain type of formula, etc.)? I don't think mapping all the
colors makes sense, so I'm looking for whatever it is that tells you to
color these cells so that code can be developed to recognize this common
element in order to reapply the colors.

--
Rick (MVP - Excel)


"Jock" <(E-Mail Removed)> wrote in message
news:0ED2FC7A-0A03-4CAC-8F55-(E-Mail Removed)...
> Ideally, yes; if only it were that simple.
> I've used CF on quite a few columns already to flag when a date is out of
> tolerence - hence the request for code.
> Thanks though, Patrick
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Patrick Molloy" wrote:
>
>> use conditional formatting
>> select columnds D:AD
>> set the 'cell value is' to 'formula is' and then the formula to
>>
>> =$AD1-"W" and then set a pattern color
>> add two more conditions like this for the other two letter patterns
>>
>> conditional formatting allows three tests, so you got lucky!
>>
>>
>>
>> "Jock" wrote:
>>
>> > This is a repeat post from yesterday which I'm still struggling with.
>> > Gary"s
>> > Student offered some help but:
>> > Sheet uses columns "A-AD", rows 4-10000.
>> > Several sets of adjacent columns are shaded (visually grouped together)
>> > to
>> > give clarity for the user.
>> > Here's the premise:
>> > Using a formula in column "AB", either "W", "SD", "ST" or "" will be
>> > returned.
>> >
>> > I need code to look at column "AB" and colour the row (from "A:AD"
>> > only)
>> > red, orange or blue for the first 3 options or leave as is for "".
>> > This part is straightforward ChangeEvent code. However, and this is the
>> > bit
>> > I can't fathom, if a cell in "AB" is changed from any of the 3 options
>> > back
>> > to blank, the original cell shading needs to be re-applied.
>> > Row 3 is the header row for all the columns, so could the fill colour
>> > from
>> > that row be used in the code to correctly re-shade the changed row?
>> > I can email worksheet if req'd.
>> > Thanks
>> > Traa Dy Liooar
>> >
>> > Jock

Hi Rick,
colouring in the columns has been done manually. It isn't triggered by
anything, just done so to make a large worksheet easily understandable to the
user.
I suggested copying the colour format from row 3 because this is the header
row and is formatted the same as the rows beneath but this formatting will
not change whereas, any of the rows beneath could depending on what happens
in "AB".

--
Traa Dy Liooar

Jock


"Rick Rothstein" wrote:

> How did the shading in columns A:AD get into the cells... conditional
> formatting or manual placed? If manually placed, is there some common item
> that can be used to identify these colored cells (type of values they
> contain, certain type of formula, etc.)? I don't think mapping all the
> colors makes sense, so I'm looking for whatever it is that tells you to
> color these cells so that code can be developed to recognize this common
> element in order to reapply the colors.
>
> --
> Rick (MVP - Excel)
>
>
> "Jock" <(E-Mail Removed)> wrote in message
> news:0ED2FC7A-0A03-4CAC-8F55-(E-Mail Removed)...
> > Ideally, yes; if only it were that simple.
> > I've used CF on quite a few columns already to flag when a date is out of
> > tolerence - hence the request for code.
> > Thanks though, Patrick
> > --
> > Traa Dy Liooar
> >
> > Jock
> >
> >
> > "Patrick Molloy" wrote:
> >
> >> use conditional formatting
> >> select columnds D:AD
> >> set the 'cell value is' to 'formula is' and then the formula to
> >>
> >> =$AD1-"W" and then set a pattern color
> >> add two more conditions like this for the other two letter patterns
> >>
> >> conditional formatting allows three tests, so you got lucky!
> >>
> >>
> >>
> >> "Jock" wrote:
> >>
> >> > This is a repeat post from yesterday which I'm still struggling with.
> >> > Gary"s
> >> > Student offered some help but:
> >> > Sheet uses columns "A-AD", rows 4-10000.
> >> > Several sets of adjacent columns are shaded (visually grouped together)
> >> > to
> >> > give clarity for the user.
> >> > Here's the premise:
> >> > Using a formula in column "AB", either "W", "SD", "ST" or "" will be
> >> > returned.
> >> >
> >> > I need code to look at column "AB" and colour the row (from "A:AD"
> >> > only)
> >> > red, orange or blue for the first 3 options or leave as is for "".
> >> > This part is straightforward ChangeEvent code. However, and this is the
> >> > bit
> >> > I can't fathom, if a cell in "AB" is changed from any of the 3 options
> >> > back
> >> > to blank, the original cell shading needs to be re-applied.
> >> > Row 3 is the header row for all the columns, so could the fill colour
> >> > from
> >> > that row be used in the code to correctly re-shade the changed row?
> >> > I can email worksheet if req'd.
> >> > Thanks
> >> > Traa Dy Liooar
> >> >
> >> > Jock
>
>

Okay, good... you have the columns colored as opposed to select areas...
that makes it easier. Just copy Row 3 and then use PasteSpecial to put the
copied format into the row you are removing your color from. Something like
this (where X is assumed to be your variable containing the row number being
processed)...

Rows(3).Copy
Rows(X).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Cells(X, 1).Select

--
Rick (MVP - Excel)


"Jock" <(E-Mail Removed)> wrote in message
news:31495A3A-043A-4E91-8729-(E-Mail Removed)...
> Hi Rick,
> colouring in the columns has been done manually. It isn't triggered by
> anything, just done so to make a large worksheet easily understandable to
> the
> user.
> I suggested copying the colour format from row 3 because this is the
> header
> row and is formatted the same as the rows beneath but this formatting will
> not change whereas, any of the rows beneath could depending on what
> happens
> in "AB".
>
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Rick Rothstein" wrote:
>
>> How did the shading in columns A:AD get into the cells... conditional
>> formatting or manual placed? If manually placed, is there some common
>> item
>> that can be used to identify these colored cells (type of values they
>> contain, certain type of formula, etc.)? I don't think mapping all the
>> colors makes sense, so I'm looking for whatever it is that tells you to
>> color these cells so that code can be developed to recognize this common
>> element in order to reapply the colors.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Jock" <(E-Mail Removed)> wrote in message
>> news:0ED2FC7A-0A03-4CAC-8F55-(E-Mail Removed)...
>> > Ideally, yes; if only it were that simple.
>> > I've used CF on quite a few columns already to flag when a date is out
>> > of
>> > tolerence - hence the request for code.
>> > Thanks though, Patrick
>> > --
>> > Traa Dy Liooar
>> >
>> > Jock
>> >
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> use conditional formatting
>> >> select columnds D:AD
>> >> set the 'cell value is' to 'formula is' and then the formula to
>> >>
>> >> =$AD1-"W" and then set a pattern color
>> >> add two more conditions like this for the other two letter patterns
>> >>
>> >> conditional formatting allows three tests, so you got lucky!
>> >>
>> >>
>> >>
>> >> "Jock" wrote:
>> >>
>> >> > This is a repeat post from yesterday which I'm still struggling
>> >> > with.
>> >> > Gary"s
>> >> > Student offered some help but:
>> >> > Sheet uses columns "A-AD", rows 4-10000.
>> >> > Several sets of adjacent columns are shaded (visually grouped
>> >> > together)
>> >> > to
>> >> > give clarity for the user.
>> >> > Here's the premise:
>> >> > Using a formula in column "AB", either "W", "SD", "ST" or "" will be
>> >> > returned.
>> >> >
>> >> > I need code to look at column "AB" and colour the row (from "A:AD"
>> >> > only)
>> >> > red, orange or blue for the first 3 options or leave as is for "".
>> >> > This part is straightforward ChangeEvent code. However, and this is
>> >> > the
>> >> > bit
>> >> > I can't fathom, if a cell in "AB" is changed from any of the 3
>> >> > options
>> >> > back
>> >> > to blank, the original cell shading needs to be re-applied.
>> >> > Row 3 is the header row for all the columns, so could the fill
>> >> > colour
>> >> > from
>> >> > that row be used in the code to correctly re-shade the changed row?
>> >> > I can email worksheet if req'd.
>> >> > Thanks
>> >> > Traa Dy Liooar
>> >> >
>> >> > Jock
>>
>>

Off home now, but will look at this tomorrow.

Thanks Rick
--
Traa Dy Liooar

Jock


"Rick Rothstein" wrote:

> Okay, good... you have the columns colored as opposed to select areas...
> that makes it easier. Just copy Row 3 and then use PasteSpecial to put the
> copied format into the row you are removing your color from. Something like
> this (where X is assumed to be your variable containing the row number being
> processed)...
>
> Rows(3).Copy
> Rows(X).PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> Cells(X, 1).Select
>
> --
> Rick (MVP - Excel)
>
>
> "Jock" <(E-Mail Removed)> wrote in message
> news:31495A3A-043A-4E91-8729-(E-Mail Removed)...
> > Hi Rick,
> > colouring in the columns has been done manually. It isn't triggered by
> > anything, just done so to make a large worksheet easily understandable to
> > the
> > user.
> > I suggested copying the colour format from row 3 because this is the
> > header
> > row and is formatted the same as the rows beneath but this formatting will
> > not change whereas, any of the rows beneath could depending on what
> > happens
> > in "AB".
> >
> > --
> > Traa Dy Liooar
> >
> > Jock
> >
> >
> > "Rick Rothstein" wrote:
> >
> >> How did the shading in columns A:AD get into the cells... conditional
> >> formatting or manual placed? If manually placed, is there some common
> >> item
> >> that can be used to identify these colored cells (type of values they
> >> contain, certain type of formula, etc.)? I don't think mapping all the
> >> colors makes sense, so I'm looking for whatever it is that tells you to
> >> color these cells so that code can be developed to recognize this common
> >> element in order to reapply the colors.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Jock" <(E-Mail Removed)> wrote in message
> >> news:0ED2FC7A-0A03-4CAC-8F55-(E-Mail Removed)...
> >> > Ideally, yes; if only it were that simple.
> >> > I've used CF on quite a few columns already to flag when a date is out
> >> > of
> >> > tolerence - hence the request for code.
> >> > Thanks though, Patrick
> >> > --
> >> > Traa Dy Liooar
> >> >
> >> > Jock
> >> >
> >> >
> >> > "Patrick Molloy" wrote:
> >> >
> >> >> use conditional formatting
> >> >> select columnds D:AD
> >> >> set the 'cell value is' to 'formula is' and then the formula to
> >> >>
> >> >> =$AD1-"W" and then set a pattern color
> >> >> add two more conditions like this for the other two letter patterns
> >> >>
> >> >> conditional formatting allows three tests, so you got lucky!
> >> >>
> >> >>
> >> >>
> >> >> "Jock" wrote:
> >> >>
> >> >> > This is a repeat post from yesterday which I'm still struggling
> >> >> > with.
> >> >> > Gary"s
> >> >> > Student offered some help but:
> >> >> > Sheet uses columns "A-AD", rows 4-10000.
> >> >> > Several sets of adjacent columns are shaded (visually grouped
> >> >> > together)
> >> >> > to
> >> >> > give clarity for the user.
> >> >> > Here's the premise:
> >> >> > Using a formula in column "AB", either "W", "SD", "ST" or "" will be
> >> >> > returned.
> >> >> >
> >> >> > I need code to look at column "AB" and colour the row (from "A:AD"
> >> >> > only)
> >> >> > red, orange or blue for the first 3 options or leave as is for "".
> >> >> > This part is straightforward ChangeEvent code. However, and this is
> >> >> > the
> >> >> > bit
> >> >> > I can't fathom, if a cell in "AB" is changed from any of the 3
> >> >> > options
> >> >> > back
> >> >> > to blank, the original cell shading needs to be re-applied.
> >> >> > Row 3 is the header row for all the columns, so could the fill
> >> >> > colour
> >> >> > from
> >> >> > that row be used in the code to correctly re-shade the changed row?
> >> >> > I can email worksheet if req'd.
> >> >> > Thanks
> >> >> > Traa Dy Liooar
> >> >> >
> >> >> > Jock
> >>
> >>
>
>

Nope, can't figure it out.
You'll probably look at the code and spot the issue straight away!!
Anyway, here's what I've got:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellColour As Variant
If Not Intersect(Target, ActiveSheet.Column("AB")) Is Nothing Then
'choose cell colour based on formula in AB
If Target.Value = "w" Then
CellColour = 3 'red
ElseIf Target.Value = "sd" Then
CellColour = 46 'orange
ElseIf Target.Value = "so" Then
CellColour = 6 'yellow
Else
[stuck here!]

End If
'colour the rows
Set myRow = Target.Offset(0, -28).Resize(, 2) 'go back to "A"
With myRow
.Interior.ColorIndex = CellColour
End With
End If
End Sub


HELP!! :0
--
Traa Dy Liooar

Jock


"Rick Rothstein" wrote:

> Okay, good... you have the columns colored as opposed to select areas...
> that makes it easier. Just copy Row 3 and then use PasteSpecial to put the
> copied format into the row you are removing your color from. Something like
> this (where X is assumed to be your variable containing the row number being
> processed)...
>
> Rows(3).Copy
> Rows(X).PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> Cells(X, 1).Select
>
> --
> Rick (MVP - Excel)
>
>
> "Jock" <(E-Mail Removed)> wrote in message
> news:31495A3A-043A-4E91-8729-(E-Mail Removed)...
> > Hi Rick,
> > colouring in the columns has been done manually. It isn't triggered by
> > anything, just done so to make a large worksheet easily understandable to
> > the
> > user.
> > I suggested copying the colour format from row 3 because this is the
> > header
> > row and is formatted the same as the rows beneath but this formatting will
> > not change whereas, any of the rows beneath could depending on what
> > happens
> > in "AB".
> >
> > --
> > Traa Dy Liooar
> >
> > Jock
> >
> >
> > "Rick Rothstein" wrote:
> >
> >> How did the shading in columns A:AD get into the cells... conditional
> >> formatting or manual placed? If manually placed, is there some common
> >> item
> >> that can be used to identify these colored cells (type of values they
> >> contain, certain type of formula, etc.)? I don't think mapping all the
> >> colors makes sense, so I'm looking for whatever it is that tells you to
> >> color these cells so that code can be developed to recognize this common
> >> element in order to reapply the colors.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Jock" <(E-Mail Removed)> wrote in message
> >> news:0ED2FC7A-0A03-4CAC-8F55-(E-Mail Removed)...
> >> > Ideally, yes; if only it were that simple.
> >> > I've used CF on quite a few columns already to flag when a date is out
> >> > of
> >> > tolerence - hence the request for code.
> >> > Thanks though, Patrick
> >> > --
> >> > Traa Dy Liooar
> >> >
> >> > Jock
> >> >
> >> >
> >> > "Patrick Molloy" wrote:
> >> >
> >> >> use conditional formatting
> >> >> select columnds D:AD
> >> >> set the 'cell value is' to 'formula is' and then the formula to
> >> >>
> >> >> =$AD1-"W" and then set a pattern color
> >> >> add two more conditions like this for the other two letter patterns
> >> >>
> >> >> conditional formatting allows three tests, so you got lucky!
> >> >>
> >> >>
> >> >>
> >> >> "Jock" wrote:
> >> >>
> >> >> > This is a repeat post from yesterday which I'm still struggling
> >> >> > with.
> >> >> > Gary"s
> >> >> > Student offered some help but:
> >> >> > Sheet uses columns "A-AD", rows 4-10000.
> >> >> > Several sets of adjacent columns are shaded (visually grouped
> >> >> > together)
> >> >> > to
> >> >> > give clarity for the user.
> >> >> > Here's the premise:
> >> >> > Using a formula in column "AB", either "W", "SD", "ST" or "" will be
> >> >> > returned.
> >> >> >
> >> >> > I need code to look at column "AB" and colour the row (from "A:AD"
> >> >> > only)
> >> >> > red, orange or blue for the first 3 options or leave as is for "".
> >> >> > This part is straightforward ChangeEvent code. However, and this is
> >> >> > the
> >> >> > bit
> >> >> > I can't fathom, if a cell in "AB" is changed from any of the 3
> >> >> > options
> >> >> > back
> >> >> > to blank, the original cell shading needs to be re-applied.
> >> >> > Row 3 is the header row for all the columns, so could the fill
> >> >> > colour
> >> >> > from
> >> >> > that row be used in the code to correctly re-shade the changed row?
> >> >> > I can email worksheet if req'd.
> >> >> > Thanks
> >> >> > Traa Dy Liooar
> >> >> >
> >> >> > Jock
> >>
> >>
>
>

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellColour As Variant
Dim correct As Range
Set correct = Intersect(Target, Range("AB:AB"))
If Not correct Is Nothing Then
'choose cell colour based on formula in AB
Select Case UCase(Target.Value)
Case "W"
CellColour = 3 'red
Case "SD"
CellColour = 46 'orange
Case "SO"
CellColour = 6 'yellow
Case Else
[stuck here!]
CellColour = xlNone
End Select
'colour the rows
With Rows(Target.Row)

.Interior.ColorIndex = CellColour
End With
End If
End Sub


"Jock" <(E-Mail Removed)> wrote in message
news:8008BBC2-9B50-4377-A638-(E-Mail Removed)...
> Nope, can't figure it out.
> You'll probably look at the code and spot the issue straight away!!
> Anyway, here's what I've got:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim CellColour As Variant
> If Not Intersect(Target, ActiveSheet.Column("AB")) Is Nothing Then
> 'choose cell colour based on formula in AB
> If Target.Value = "w" Then
> CellColour = 3 'red
> ElseIf Target.Value = "sd" Then
> CellColour = 46 'orange
> ElseIf Target.Value = "so" Then
> CellColour = 6 'yellow
> Else
> [stuck here!]
>
> End If
> 'colour the rows
> Set myRow = Target.Offset(0, -28).Resize(, 2) 'go back to "A"
> With myRow
> .Interior.ColorIndex = CellColour
> End With
> End If
> End Sub
>
>
> HELP!! :0
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Rick Rothstein" wrote:
>
>> Okay, good... you have the columns colored as opposed to select areas...
>> that makes it easier. Just copy Row 3 and then use PasteSpecial to put
>> the
>> copied format into the row you are removing your color from. Something
>> like
>> this (where X is assumed to be your variable containing the row number
>> being
>> processed)...
>>
>> Rows(3).Copy
>> Rows(X).PasteSpecial xlPasteFormats
>> Application.CutCopyMode = False
>> Cells(X, 1).Select
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Jock" <(E-Mail Removed)> wrote in message
>> news:31495A3A-043A-4E91-8729-(E-Mail Removed)...
>> > Hi Rick,
>> > colouring in the columns has been done manually. It isn't triggered by
>> > anything, just done so to make a large worksheet easily understandable
>> > to
>> > the
>> > user.
>> > I suggested copying the colour format from row 3 because this is the
>> > header
>> > row and is formatted the same as the rows beneath but this formatting
>> > will
>> > not change whereas, any of the rows beneath could depending on what
>> > happens
>> > in "AB".
>> >
>> > --
>> > Traa Dy Liooar
>> >
>> > Jock
>> >
>> >
>> > "Rick Rothstein" wrote:
>> >
>> >> How did the shading in columns A:AD get into the cells... conditional
>> >> formatting or manual placed? If manually placed, is there some common
>> >> item
>> >> that can be used to identify these colored cells (type of values they
>> >> contain, certain type of formula, etc.)? I don't think mapping all the
>> >> colors makes sense, so I'm looking for whatever it is that tells you
>> >> to
>> >> color these cells so that code can be developed to recognize this
>> >> common
>> >> element in order to reapply the colors.
>> >>
>> >> --
>> >> Rick (MVP - Excel)
>> >>
>> >>
>> >> "Jock" <(E-Mail Removed)> wrote in message
>> >> news:0ED2FC7A-0A03-4CAC-8F55-(E-Mail Removed)...
>> >> > Ideally, yes; if only it were that simple.
>> >> > I've used CF on quite a few columns already to flag when a date is
>> >> > out
>> >> > of
>> >> > tolerence - hence the request for code.
>> >> > Thanks though, Patrick
>> >> > --
>> >> > Traa Dy Liooar
>> >> >
>> >> > Jock
>> >> >
>> >> >
>> >> > "Patrick Molloy" wrote:
>> >> >
>> >> >> use conditional formatting
>> >> >> select columnds D:AD
>> >> >> set the 'cell value is' to 'formula is' and then the formula to
>> >> >>
>> >> >> =$AD1-"W" and then set a pattern color
>> >> >> add two more conditions like this for the other two letter patterns
>> >> >>
>> >> >> conditional formatting allows three tests, so you got lucky!
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Jock" wrote:
>> >> >>
>> >> >> > This is a repeat post from yesterday which I'm still struggling
>> >> >> > with.
>> >> >> > Gary"s
>> >> >> > Student offered some help but:
>> >> >> > Sheet uses columns "A-AD", rows 4-10000.
>> >> >> > Several sets of adjacent columns are shaded (visually grouped
>> >> >> > together)
>> >> >> > to
>> >> >> > give clarity for the user.
>> >> >> > Here's the premise:
>> >> >> > Using a formula in column "AB", either "W", "SD", "ST" or "" will
>> >> >> > be
>> >> >> > returned.
>> >> >> >
>> >> >> > I need code to look at column "AB" and colour the row (from
>> >> >> > "A:AD"
>> >> >> > only)
>> >> >> > red, orange or blue for the first 3 options or leave as is for
>> >> >> > "".
>> >> >> > This part is straightforward ChangeEvent code. However, and this
>> >> >> > is
>> >> >> > the
>> >> >> > bit
>> >> >> > I can't fathom, if a cell in "AB" is changed from any of the 3
>> >> >> > options
>> >> >> > back
>> >> >> > to blank, the original cell shading needs to be re-applied.
>> >> >> > Row 3 is the header row for all the columns, so could the fill
>> >> >> > colour
>> >> >> > from
>> >> >> > that row be used in the code to correctly re-shade the changed
>> >> >> > row?
>> >> >> > I can email worksheet if req'd.
>> >> >> > Thanks
>> >> >> > Traa Dy Liooar
>> >> >> >
>> >> >> > Jock
>> >>
>> >>
>>
>>