I have various rows which are colour coded and I have added several columns
on to the end - One for a checker to initial and several others for
approvals, comments and date.

I want the checker Cell to change colour when initials are entered (e.g.
=B1>0) I want the remaining cells on the row to change colour when approval
initails are entered. I can do this with conditional formatting. The problem
is I have been using Easyfilter to filter the colours - but when I try to
copy formats it is picking up the hidden rows.

I have resorted to copying and pasting the formatting only, but this is
going to take days. Is there a way to conditional format the colour, to match
the colour of another cell in the row?

Regards
Dylan Dawson
Scotland

I don't follow why you can't CF all cells in the row the same as your
checker cell. Otherwise describe more clearly what you've got and what
formats you want to change under what condition(s).

Regards,
Peter T

"DDawson" <(E-Mail Removed)> wrote in message
news:50647872-47F5-4011-A1DD-(E-Mail Removed)...
> I have various rows which are colour coded and I have added several
columns
> on to the end - One for a checker to initial and several others for
> approvals, comments and date.
>
> I want the checker Cell to change colour when initials are entered (e.g.
> =B1>0) I want the remaining cells on the row to change colour when
approval
> initails are entered. I can do this with conditional formatting. The
problem
> is I have been using Easyfilter to filter the colours - but when I try to
> copy formats it is picking up the hidden rows.
>
> I have resorted to copying and pasting the formatting only, but this is
> going to take days. Is there a way to conditional format the colour, to
match
> the colour of another cell in the row?
>
> Regards
> Dylan Dawson
> Scotland
>

I Assume you mean the source cell that is used for the copy, when you say
checker cell.

When I copy format down a column, it copies the colour from the checker cell
down the entire row.

When I use Easyfilter, and repeat this process, it copies the formatting to
the filtered/ hidden rows.

I want to set up a conditional format which will pick up the colour of the
adjacent cell and use it, insteadof the colour of the checker cell.

"Peter T" wrote:

> I don't follow why you can't CF all cells in the row the same as your
> checker cell. Otherwise describe more clearly what you've got and what
> formats you want to change under what condition(s).
>
> Regards,
> Peter T
>
> "DDawson" <(E-Mail Removed)> wrote in message
> news:50647872-47F5-4011-A1DD-(E-Mail Removed)...
> > I have various rows which are colour coded and I have added several
> columns
> > on to the end - One for a checker to initial and several others for
> > approvals, comments and date.
> >
> > I want the checker Cell to change colour when initials are entered (e.g.
> > =B1>0) I want the remaining cells on the row to change colour when
> approval
> > initails are entered. I can do this with conditional formatting. The
> problem
> > is I have been using Easyfilter to filter the colours - but when I try to
> > copy formats it is picking up the hidden rows.
> >
> > I have resorted to copying and pasting the formatting only, but this is
> > going to take days. Is there a way to conditional format the colour, to
> match
> > the colour of another cell in the row?
> >
> > Regards
> > Dylan Dawson
> > Scotland
> >
>
>
>

> down the entire row.

do you mean across ?

On the basis of slightly improved understanding a worksheet change event
might be a better approach. When I suggested "describe more clearly what
you've got" I meant example including (simplified) cell ref's of cells with
format to be copied, to where and on the basis of what change in which
cell(s)

Regards,
Peter T

"DDawson" <(E-Mail Removed)> wrote in message
news:4510F753-B94C-46DE-8A80-(E-Mail Removed)...
> I Assume you mean the source cell that is used for the copy, when you say
> checker cell.
>
> When I copy format down a column, it copies the colour from the checker
cell
> down the entire row.
>
> When I use Easyfilter, and repeat this process, it copies the formatting
to
> the filtered/ hidden rows.
>
> I want to set up a conditional format which will pick up the colour of the
> adjacent cell and use it, insteadof the colour of the checker cell.
>
> "Peter T" wrote:
>
> > I don't follow why you can't CF all cells in the row the same as your
> > checker cell. Otherwise describe more clearly what you've got and what
> > formats you want to change under what condition(s).
> >
> > Regards,
> > Peter T
> >
> > "DDawson" <(E-Mail Removed)> wrote in message
> > news:50647872-47F5-4011-A1DD-(E-Mail Removed)...
> > > I have various rows which are colour coded and I have added several
> > columns
> > > on to the end - One for a checker to initial and several others for
> > > approvals, comments and date.
> > >
> > > I want the checker Cell to change colour when initials are entered
(e.g.
> > > =B1>0) I want the remaining cells on the row to change colour when
> > approval
> > > initails are entered. I can do this with conditional formatting. The
> > problem
> > > is I have been using Easyfilter to filter the colours - but when I try
to
> > > copy formats it is picking up the hidden rows.
> > >
> > > I have resorted to copying and pasting the formatting only, but this
is
> > > going to take days. Is there a way to conditional format the colour,
to
> > match
> > > the colour of another cell in the row?
> > >
> > > Regards
> > > Dylan Dawson
> > > Scotland
> > >
> >
> >
> >

Peter,

Thanks for your advice and assistance, I think an event procedure would
work. As a rough explanation:

For the "Checked" Column (Column F)
A change procedure whereby; If a cell in column F changes from being blank
then it will also change colour to match the colour of the cell in column B
of the corresponding row.

For the "Approved" Column" (Column G)
A change procedure whereby; If a cell in column G changes to "YES" then it
will also change colour to match the colour of the cell in column B of the
corresponding row.

Does this make any sense?

PS: "Peter T" wrote:

> > down the entire row.
>
> do you mean across ?
>
Yes across.

Dylan D

OK Dylan, think I follow now

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
Dim cel As Range
Dim rng As Range
On Error GoTo errExit

' change "F1" to the first cell in col F to process
Set rng = Intersect(Range(Range("F1"), _
Cells(Range("B65536").End(xlUp).Row, 7)), Target)

If Not rng Is Nothing Then
For Each cel In rng
x = 0
With cel
If .Column = 6 Then 'col F

If .Value = "" Then
x = xlNone
Else
x = .Offset(0, -4).Interior.ColorIndex 'col B
End If

ElseIf .Column = 7 Then 'col G

If UCase(.Value) = "YES" Then
x = .Offset(0, -5).Interior.ColorIndex 'col B
Else: x = xlNone
End If

End If

If x Then
If .Interior.ColorIndex <> x Then
'only set if necessary to minimize loss of Undo
.Interior.ColorIndex = x
End If
End If
End With
Next
End If
errExit:
End Sub

Although you didn't ask this also removes colour if say Col-F cell is
deleted or Col-G cell is changed from 'Yes".

Regards,
Peter T

"DDawson" <(E-Mail Removed)> wrote in message
news:C4E32B88-FEAB-4106-B960-(E-Mail Removed)...
> Peter,
>
> Thanks for your advice and assistance, I think an event procedure would
> work. As a rough explanation:
>
> For the "Checked" Column (Column F)
> A change procedure whereby; If a cell in column F changes from being blank
> then it will also change colour to match the colour of the cell in column
B
> of the corresponding row.
>
> For the "Approved" Column" (Column G)
> A change procedure whereby; If a cell in column G changes to "YES" then it
> will also change colour to match the colour of the cell in column B of the
> corresponding row.
>
> Does this make any sense?
>
> PS: "Peter T" wrote:
>
> > > down the entire row.
> >
> > do you mean across ?
> >
> Yes across.
>
> Dylan D

Peter,

It works perfectly thanks.

One more thing (as Columbo would say)

How do I make columns 8-13 change colour when "YES" is added to Column 7?

Much appreciated
Dylan D

"Peter T" wrote:

> OK Dylan, think I follow now
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim x As Long
> Dim cel As Range
> Dim rng As Range
> On Error GoTo errExit
>
> ' change "F1" to the first cell in col F to process
> Set rng = Intersect(Range(Range("F1"), _
> Cells(Range("B65536").End(xlUp).Row, 7)), Target)
>
> If Not rng Is Nothing Then
> For Each cel In rng
> x = 0
> With cel
> If .Column = 6 Then 'col F
>
> If .Value = "" Then
> x = xlNone
> Else
> x = .Offset(0, -4).Interior.ColorIndex 'col B
> End If
>
> ElseIf .Column = 7 Then 'col G
>
> If UCase(.Value) = "YES" Then
> x = .Offset(0, -5).Interior.ColorIndex 'col B
> Else: x = xlNone
> End If
>
> End If
>
> If x Then
> If .Interior.ColorIndex <> x Then
> 'only set if necessary to minimize loss of Undo
> .Interior.ColorIndex = x
> End If
> End If
> End With
> Next
> End If
> errExit:
> End Sub
>
> Although you didn't ask this also removes colour if say Col-F cell is
> deleted or Col-G cell is changed from 'Yes".
>
> Regards,
> Peter T
>
> "DDawson" <(E-Mail Removed)> wrote in message
> news:C4E32B88-FEAB-4106-B960-(E-Mail Removed)...
> > Peter,
> >
> > Thanks for your advice and assistance, I think an event procedure would
> > work. As a rough explanation:
> >
> > For the "Checked" Column (Column F)
> > A change procedure whereby; If a cell in column F changes from being blank
> > then it will also change colour to match the colour of the cell in column
> B
> > of the corresponding row.
> >
> > For the "Approved" Column" (Column G)
> > A change procedure whereby; If a cell in column G changes to "YES" then it
> > will also change colour to match the colour of the cell in column B of the
> > corresponding row.
> >
> > Does this make any sense?
> >
> > PS: "Peter T" wrote:
> >
> > > > down the entire row.
> > >
> > > do you mean across ?
> > >
> > Yes across.
> >
> > Dylan D
>
>
>

> How do I make columns 8-13 change colour when "YES" is added to Column 7?

so including Col-G as before that's 7 columns 7-13

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long, v As Variant
Dim cel As Range
Dim rng As Range
On Error GoTo errExit

Set rng = Intersect(Range(Range("F1"), _
Cells(Range("B65536").End(xlUp).Row, 7)), Target)

If Not rng Is Nothing Then
For Each cel In rng
x = 0
With cel
If .Column = 6 Then 'col F

If .Value = "" Then
x = xlNone
Else
x = .Offset(0, -4).Interior.ColorIndex 'col B
End If
If .Interior.ColorIndex <> x Then
.Interior.ColorIndex = x
End If

ElseIf .Column = 7 Then 'col G

If UCase(.Value) = "YES" Then
x = .Offset(0, -5).Interior.ColorIndex 'col B
Else: x = xlNone
End If
With .Resize(1, 7)
v = .Interior.ColorIndex
If IsNull(v) Then v = -1
If v <> x Then
.Interior.ColorIndex = x
End If
End With
End If

If x Then
End If
End With
Next
End If
errExit:
End Sub

Regards,
Peter T

"DDawson" <(E-Mail Removed)> wrote in message
news:45AF7706-A6AC-4DF6-875E-(E-Mail Removed)...
> Peter,
>
> It works perfectly thanks.
>
> One more thing (as Columbo would say)
>
> How do I make columns 8-13 change colour when "YES" is added to Column 7?
>
> Much appreciated
> Dylan D
>
> "Peter T" wrote:
>
> > OK Dylan, think I follow now
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim x As Long
> > Dim cel As Range
> > Dim rng As Range
> > On Error GoTo errExit
> >
> > ' change "F1" to the first cell in col F to process
> > Set rng = Intersect(Range(Range("F1"), _
> > Cells(Range("B65536").End(xlUp).Row, 7)), Target)
> >
> > If Not rng Is Nothing Then
> > For Each cel In rng
> > x = 0
> > With cel
> > If .Column = 6 Then 'col F
> >
> > If .Value = "" Then
> > x = xlNone
> > Else
> > x = .Offset(0, -4).Interior.ColorIndex 'col B
> > End If
> >
> > ElseIf .Column = 7 Then 'col G
> >
> > If UCase(.Value) = "YES" Then
> > x = .Offset(0, -5).Interior.ColorIndex 'col B
> > Else: x = xlNone
> > End If
> >
> > End If
> >
> > If x Then
> > If .Interior.ColorIndex <> x Then
> > 'only set if necessary to minimize loss of Undo
> > .Interior.ColorIndex = x
> > End If
> > End If
> > End With
> > Next
> > End If
> > errExit:
> > End Sub
> >
> > Although you didn't ask this also removes colour if say Col-F cell is
> > deleted or Col-G cell is changed from 'Yes".
> >
> > Regards,
> > Peter T
> >
> > "DDawson" <(E-Mail Removed)> wrote in message
> > news:C4E32B88-FEAB-4106-B960-(E-Mail Removed)...
> > > Peter,
> > >
> > > Thanks for your advice and assistance, I think an event procedure
would
> > > work. As a rough explanation:
> > >
> > > For the "Checked" Column (Column F)
> > > A change procedure whereby; If a cell in column F changes from being
blank
> > > then it will also change colour to match the colour of the cell in
column
> > B
> > > of the corresponding row.
> > >
> > > For the "Approved" Column" (Column G)
> > > A change procedure whereby; If a cell in column G changes to "YES"
then it
> > > will also change colour to match the colour of the cell in column B of
the
> > > corresponding row.
> > >
> > > Does this make any sense?
> > >
> > > PS: "Peter T" wrote:
> > >
> > > > > down the entire row.
> > > >
> > > > do you mean across ?
> > > >
> > > Yes across.
> > >
> > > Dylan D
> >
> >
> >

Peter

Thank you for helping me with this.

Regards
Dylan