Highlighting postcodes on a map

[monkeyharris]

Dear all,

I have a postcode map that shows UK postcodes inside each zone and a corresponding list.
There is a search field where the user enters the postcode and what i want is for the corresponding map square to change colour for easy and quick reference to where it is.
Under this i know how to make a field show which branch is closest to the chosen postcode, i'm just struggling with how to make the image change colour. I couldn't copy the map into a mini sheet so attached an image.

IDS Postcode Map.xlsx
	T	U	V	W	X	Y
56
57	Enter Postcode		RH		Swindon	RG
58	IDS Branch				Swindon	SL
59					Swindon	OX
60					Woodford	CM
61					Woodford	RM
62					Woodford	SG
General Tariff

 

[monkeyharris]

Opps! Just noticed that the above code has my test entry cell reference of V5 rather than your desired V57
Just edit that line as below

 If Intersect(Range("V57"), Target) Is Nothing Then Exit Sub

Hi,
I know i'm doing something wrong. I've put the code in the worksheet and made the amendment to V57. I enter CT in V57 and nothing happens.
Do i need to do something with the images? At the moment they are saved in a file called CT, DA, ME etc then placed in the workbook.

 

[monkeyharris]

Opps! Just noticed that the above code has my test entry cell reference of V5 rather than your desired V57
Just edit that line as below

 If Intersect(Range("V57"), Target) Is Nothing Then Exit Sub

Morning,
I must be doing something wrong as i put the code in the worksheet VBA window, made the amendment to V57, but when i enter a postcode in V57 nothing happens.
Is there something special i need to do to each drawing/Postcode image?

 

[Snakehips]

@monkeyharris I had assumed that your postcode elements would be shapes similar to map element shapes that were in a post I responded to a couple of weeks back.
Colour of shape not changing
I'm not sure I have an immediate answer given that your postcode elements are images. I assume transparent backgrounds?

I will give it some thought and get back to you if I feel I can help.

 

[Snakehips]

@monkeyharris. Did you get to sort this?
I have had no luck in coding to change a picture colour in the same way as works with shapes.
I do however have a bit of an idea if you are still wanting to pursue it?

 

[monkeyharris]

@monkeyharris. Did you get to sort this?
I have had no luck in coding to change a picture colour in the same way as works with shapes.
I do however have a bit of an idea if you are still wanting to pursue it?

Hi Snakeips,

I certainly do want to pursue it. If there is something different i need to do or obtain another type of map that would be fine.

 

[Snakehips]

@monkeyharris In the light of my new understanding re entry cell being V61 etc I now propose the following modified code.

Sub Worksheet_Change(ByVal Target As Range)
Dim NewCode As String

    ' Check if 'Target' is the Entry cell entry cell V61
    'If not then exit , do nothing
    If Intersect(Range("V61"), Target) Is Nothing Then Exit Sub
    Debug.Print Target.Cells.Count
    If Target.Cells.Count > 1 Then Exit Sub
    
 'Otherwise
 On Error Resume Next 'ignore errors if code not valid shape name
 'reset default colours
 ActiveSheet.Shapes("UKGroup").Fill.ForeColor.RGB = vbWhite
 ActiveSheet.Shapes("CLonGroup").Fill.ForeColor.RGB = vbRed
 ActiveSheet.Shapes("LondonGroup").Fill.ForeColor.RGB = vbRed
' lookup get area shape name
NewCode = Evaluate("=VLOOKUP(V61,'Postcode Districts'!$A$2:$C$2993,2)")
 Application.ScreenUpdating = False
 Application.EnableEvents = False
      
'make selectedsegment green
If NewCode <> "" Then ActiveSheet.Shapes(NewCode).Fill.ForeColor.RGB = vbGreen

On Error GoTo 0  'reset error default
 Application.ScreenUpdating = True
 Application.EnableEvents = True

End Sub

HTH