Conditional Formatting

[MM]

Hi,

Help Please!

I've set up a sheet with conditional formatting to alter/ensure that the
data input fits into the format I want. This is working fine when the data
is "manually" input, but where the data in copied from another sheet, the
conditions that I've applied aren't applying - how can I ensure that the data
copied from another sheet amend the data to the conditions that I want to
apply?

E.g - I've set up conditional formatting to ensure that a figure to try and
ensure that the input value is between 5 and 7, (anything outside this range
I've set conditional formatting to bold it in RED). Yet when data is copied
to this cell (outside this range) the conditional formatting doesn't work.

Hope this makes sense!

Thanks,

MM

 

[Sheeloo]

Use Edit->Paste Special->Values to just copy the values...

If you simply use Paste then format of the source also gets copied over..

 

[MM]

Hi Sheeloo,

Excellent - is there anyway I can set the sheet up so that however data is
coped here, that its "mandatory" to use Edit->Paste Special->Values and not
just "paste" them in?

Thanks

 

[Gord Dibben]

For copy and paste this event code will retain the CF when cells are copied
over.

Note: does not retain the formatting when you Cut and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
On Error GoTo endit
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
endit:
Application.CutCopyMode = False
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Paste the code into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP