conditional formatting Copy colour of adjacent cell

[DDawson]

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 T]

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]

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]

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]

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?

do you mean across ?

Yes across.

Dylan D

 

[Peter T]

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]

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]

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]

Peter

Thank you for helping me with this.

Regards
Dylan