Source Formatting from a Data Validation

[CantExcel123]

I've been messing around with this for a couple days and cant figure it out. Please help.

I have a spreadsheet where I have a dropdown list (data validation) that (on sheet1) references a list of names on sheet2. I would like to keep the source formatting (i.e. yellow fill, or red text, etc) from the list of names on sheet2 when I select a name on sheet1. I've tried a couple flavors of VBA that weren't exactly my setup, mor complicated if anything, (by changing the range of cells, etc) with no luck.

Please help.

 

[Snakehips]

@CantExcel123 Welcome.
Try the below in the input sheet.
It is not overly sophistocated in as much as the dv sheet name, dv list range and input range need to be hard coded, (to be edited by you).
However, if you only need this functionality for the names it may suffice.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vws As Worksheet
Dim vrng As Range
Dim r As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A2000")) Is Nothing Then Exit Sub   '<<<<< edit range to be your input range of interest
''Otherwise proceed
Application.EnableEvents = False

Set vws = Sheets("Sheet2")  '****  validation list sheet - edit to suit
Set vrng = vws.Range("$A$2:$A$20")  '<<<<< Validation List address  - edit to suit

r = Application.WorksheetFunction.Match(Target, vrng, 0)   'row in dv list
'set same interior and font colours
Target.Interior.Color = vrng.Cells(r, 1).Interior.Color
Target.Font.Color = vrng.Cells(r, 1).Font.Color

  Application.EnableEvents = True
End Sub

Hope that helps.

 

[CantExcel123]

@CantExcel123 Welcome.
Try the below in the input sheet.
It is not overly sophistocated in as much as the dv sheet name, dv list range and input range need to be hard coded, (to be edited by you).
However, if you only need this functionality for the names it may suffice.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vws As Worksheet
Dim vrng As Range
Dim r As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A2000")) Is Nothing Then Exit Sub   '<<<<< edit range to be your input range of interest
''Otherwise proceed
Application.EnableEvents = False

Set vws = Sheets("Sheet2")  '****  validation list sheet - edit to suit
Set vrng = vws.Range("$A$2:$A$20")  '<<<<< Validation List address  - edit to suit

r = Application.WorksheetFunction.Match(Target, vrng, 0)   'row in dv list
'set same interior and font colours
Target.Interior.Color = vrng.Cells(r, 1).Interior.Color
Target.Font.Color = vrng.Cells(r, 1).Font.Color

  Application.EnableEvents = True
End Sub

Hope that helps.

Trying this now. Thank you...

 

[CantExcel123]

Seems to be working. I was even able to add Target.Font.Bold = vrng.Cells(r, 1).Font.Bold so it would automatically format that also.

Thank you very much sir.

 

[CantExcel123]

One Question - when I try and clear data from a (dropdown list) cell, be it 'clear contents' or backspace it pops an error. Is there a way to stop that ?

Runtime error 1004
Unable to match property of the Worksheetfucntion class

 

[Snakehips]

Hi, does the below sort it?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vws As Worksheet
Dim vrng As Range
Dim r As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A20")) Is Nothing Then Exit Sub   '<<<<< edit range to be your input range of interest
''Otherwise proceed
Application.EnableEvents = False

Set vws = Sheets("Sheet2")  '****  validation list sheet - edit to suit
Set vrng = vws.Range("$A$2:$A$20")  '<<<<< Validation List address  - edit to suit

On Error Resume Next  'allows for clearing a single cell
r = Application.WorksheetFunction.Match(Target, vrng, 0)   'row in dv list
'set same interior and font colours
Target.Interior.Color = vrng.Cells(r, 1).Interior.Color
Target.Font.Color = vrng.Cells(r, 1).Font.Color
On Error GoTo 0
  Application.EnableEvents = True
End Sub

 

[CantExcel123]

Hi, does the below sort it?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vws As Worksheet
Dim vrng As Range
Dim r As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A20")) Is Nothing Then Exit Sub   '<<<<< edit range to be your input range of interest
''Otherwise proceed
Application.EnableEvents = False

Set vws = Sheets("Sheet2")  '****  validation list sheet - edit to suit
Set vrng = vws.Range("$A$2:$A$20")  '<<<<< Validation List address  - edit to suit

On Error Resume Next  'allows for clearing a single cell
r = Application.WorksheetFunction.Match(Target, vrng, 0)   'row in dv list
'set same interior and font colours
Target.Interior.Color = vrng.Cells(r, 1).Interior.Color
Target.Font.Color = vrng.Cells(r, 1).Font.Color
On Error GoTo 0
  Application.EnableEvents = True
End Sub

No error when using this code, but does not pull formatting from list...

 

[Snakehips]

@CantExcel123

This is probably a bit better as it will reestablish cleared cells with gridline.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vws As Worksheet
Dim vrng As Range
Dim r As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A20")) Is Nothing Then Exit Sub   '<<<<< edit range to be your input range of interest
''Otherwise proceed
Application.EnableEvents = False

Set vws = Sheets("Sheet2")  '****  validation list sheet - edit to suit
Set vrng = vws.Range("$A$2:$A$20")  '<<<<< Validation List address  - edit to suit

On Error Resume Next  'alows single cell clearing by ignoring any error setting r
r = Application.WorksheetFunction.Match(Target, vrng, 0)   'row in dv list

If Not Err.Number = 0 Then 'there is an error setting r due to clearing
    ' so set target back to default
    Target.Interior.Pattern = xlNone
Else
    'otherwise set as per the dv pick
    Target.Interior.Color = vrng.Cells(r, 1).Interior.Color
    Target.Font.Color = vrng.Cells(r, 1).Font.Color
    Target.Font.Bold = vrng.Cells(r, 1).Font.Bold
End If

On Error GoTo 0    'reset error handling to default
Application.EnableEvents = True
End Sub

 

[CantExcel123]

No error when using this code, but does not pull formatting from list...

****edit***

That did the trick!

 

[Snakehips]

Brilliant! If you would mark the post as answered that would be great.