Copying formats of referenced cells

A

Ashin

I've a rather strange requirement. I'm using a drop-down list in my cells and
depending on what the user chooses from it, I want to change the formatting
of the cell. NO, I can't use Conditional Formatting as the no of items are
too much (>>4). So, I created a list with these items on a seperate sheet
with different formatting for each item and named it. Now, when someone
chooses one of these items from the drop down, I want the format of the cell
in the original list to get copied in the current cell.
Any solution???
 
D

Dave Peterson

I'm guessing you used Data|validation to create the dropdown.

If you use xl97, this won't work.

But if you're using xl2k or higher, rightclick on the worksheet tab that should
have this behavior and select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myList As Range
Dim res As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("F1")) Is Nothing Then Exit Sub

Set myList = ThisWorkbook.Names("myList").RefersToRange
res = Application.Match(Target.Value, myList, 0)

On Error GoTo errHandler:
If IsError(res) Then
'not found, clear the formats you just set
Else
Application.EnableEvents = False
myList(res).Copy
Target.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End If

errHandler:
Application.EnableEvents = True

End Sub

I used a list called "MyList". Change this portion to match your range name:
ThisWorkbook.Names("myList").RefersToRange

I also put the cell with Data Validation in F1.
Change this to suit:
If Intersect(Target, Me.Range("F1")) Is Nothing Then Exit Sub

See Debra Dalgleish's site for a work around (using a button near the cell) if
you're using xl97.

http://www.contextures.com/xlDataVal08.html#Change
 
Top