Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Can you Lookup/Match then copy formating?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 115
Default Can you Lookup/Match then copy formating?
I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
...
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Can you Lookup/Match then copy formating?
Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:
I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 115
Default Can you Lookup/Match then copy formating?
Almost there... this copied the background pattern only. Can I also send
over the Pattern Option (lines/dots) as well as the font color?

"Rowan" wrote:

Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:
I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Can you Lookup/Match then copy formating?
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
End With
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub


Regards
Rowan

CRayF wrote:
Almost there... this copied the background pattern only. Can I also send
over the Pattern Option (lines/dots) as well as the font color?

"Rowan" wrote:


Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:

I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 115
Default Can you Lookup/Match then copy formating?
This is great...
And what about font color?


"Rowan" wrote:

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
End With
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub


Regards
Rowan

CRayF wrote:
Almost there... this copied the background pattern only. Can I also send
over the Pattern Option (lines/dots) as well as the font color?

"Rowan" wrote:


Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:

I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 115
Default Can you Lookup/Match then copy formating?
I tried adding:
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
.Font.Color = fRng.Font.Color
End With

but that did NOT do it... ;-(

"Rowan" wrote:

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
End With
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub


Regards
Rowan

CRayF wrote:
Almost there... this copied the background pattern only. Can I also send
over the Pattern Option (lines/dots) as well as the font color?

"Rowan" wrote:


Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:

I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Can you Lookup/Match then copy formating?
That is because .Font isn't a property of interior. It is a property of the
range.

--
Regards,
Tom Ogilvy

"CRayF" wrote in message
...
I tried adding:
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
.Font.Color = fRng.Font.Color
End With

but that did NOT do it... ;-(

"Rowan" wrote:

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
End With
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub


Regards
Rowan

CRayF wrote:
Almost there... this copied the background pattern only. Can I also
send
over the Pattern Option (lines/dots) as well as the font color?

"Rowan" wrote:


Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:

I have 4 Cells which are indented to be User Picks of racer numbers
between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is
FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number
entered in
the Cells S16-S19, and lookup up the value to match a Cell in
AC12:AC19. Once
matched, change the User Picked Racer in the "S" Cell to match for
FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User
changed
the Racer number again, it would change to match again.

I already have code set up to detect change





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Can you Lookup/Match then copy formating?
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
End With
Target.Font.colorindex = fRng.font.colorindex

Although another take on this might be to copy and pastespecial the
formats from one cell to the other so:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
fRng.Copy
Target.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub


CRayF wrote:
I tried adding:
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
.Font.Color = fRng.Font.Color
End With

but that did NOT do it... ;-(

"Rowan" wrote:


Try:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
With Target.Interior
.ColorIndex = fRng.Interior.ColorIndex
.Pattern = fRng.Interior.Pattern
.PatternColorIndex = fRng.Interior.PatternColorIndex
End With
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub


Regards
Rowan

CRayF wrote:

Almost there... this copied the background pattern only. Can I also send
over the Pattern Option (lines/dots) as well as the font color?

"Rowan" wrote:



Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim fRng As Range
If Target.Count = 1 Then
Set fRng = Intersect(Target, Range("S16:S19"))
If Not fRng Is Nothing Then
Set fRng = Nothing
With Range("AC12:AC19")
Set fRng = .Find(Target.Value)
End With
If Not fRng Is Nothing Then
Target.Interior.ColorIndex = fRng.Interior.ColorIndex
End If
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

CRayF wrote:


I have 4 Cells which are indented to be User Picks of racer numbers between
1-8:
S16
S17
S18
S19

In the AC Cells each holds the racer number 1-8 (below) and is FOTMATED
(which include some Patten shading) to match the Racer Colors.
AC12 = 1
AC13 = 2
AC14 = 3
..
AC19 = 8

Within this sub() I would like to know is I could take the number entered in
the Cells S16-S19, and lookup up the value to match a Cell in AC12:AC19. Once
matched, change the User Picked Racer in the S Cell to match for FORMAT of
the matching AC Cell.

Also, I would not want to overlay the formula so that if the User changed
the Racer number again, it would change to match again.

I already have code set up to detect change


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Formula: Return 1st match, then 2nd match, then 3rd match Scott Excel Discussion (Misc queries) 4 December 11th 09 05:50 AM
Conditional formating, match, lookups ACCAguy Excel Worksheet Functions 10 September 9th 08 08:59 PM
Lookup? Match? pulling rows from one spreadsheet to match a text f cjax Excel Worksheet Functions 3 July 21st 06 02:51 PM
Lookup and conditional formating Dan Excel Worksheet Functions 2 March 27th 06 03:15 AM
Copy Rows if Lookup Criteria Match vdoubleu Excel Discussion (Misc queries) 0 March 29th 05 06:43 PM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement