Add another value from a cell onto the value from a data validation list

[jamobe]

Hi all

Could someone please help me with trying to achieve the below scenario

I have a colour in cell A2 ie blue, in cell A3 I have a data validation list with various selections, colour, month, size etc

When I select from the list in A3 I would like A3 to also have the value in A2 attached on the end so it will look like this in A3 'colour=blue.

Cheers!

 

[jasonb75]

You would need a worksheet change event for that.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Target.Address(0, 0) = "A3" Then Target.Value = Target.Value & "=" & Range("A2").Value
Application.EnableEvents = True
End Sub

 

[jamobe]

You would need a worksheet change event for that.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Target.Address(0, 0) = "A3" Then Target.Value = Target.Value & "=" & Range("A2").Value
Application.EnableEvents = True
End Sub

Thanks very much for that code. That seems very complex, for now I will just stick with having an extra column and using a formula in A4 like =A2&A3. Just out of interest, would that vba code slow the worksheet down for something achieving the same result with having an extra column? Or will it make no difference? My worksheet can become very slow due to the size of it so am curious.

 

[jasonb75]

On something so simple the difference in speed would be too small to measure.

Bear in mind that it is only for a single cell (as per your question), if you wanted to apply the same rule to any cell in a specific column you may find that the vba method will be more efficient.
That said, slow performance would most likely be caused by other things. Excessively large arrays, heavy reliance on volatile functions, custom formats applied to large (unused) ranges, etc.

 

[jamobe]

On something so simple the difference in speed would be too small to measure.

Bear in mind that it is only for a single cell (as per your question), if you wanted to apply the same rule to any cell in a specific column you may find that the vba method will be more efficient.
That said, slow performance would most likely be caused by other things. Excessively large arrays, heavy reliance on volatile functions, custom formats applied to large (unused) ranges, etc.

Thanks that's interesting to know, it would actually be for many cells. I will look at using vba for things like this as with other functions I use on my worksheet.

 

[jasonb75]

Thanks that's interesting to know, it would actually be for many cells.

In that case there would be things that might affect performance. As an example, for a continuous range of cells the Intersect method should be much faster than using If for each individual cell.