Clear data displaying after a Ctrl F 'Find' search

[Neil Harrison]

Hi,

just starting to use excel 'functionality' on a large price list which has numerous suppliers each with their numerous product items and each supplier with differing discount levels.
Only 1 Tab of their worksbook is unlocked and provided for our use and manipulation (this Tab has every item of equipment listed at the usual cost price), all the other locked Tabs display 'unique to the tab' types of equipment (ie 1 tab for equipment types A, 1 tab for types B, 1 tab for types C etc.) These state each of those items at the usual retail cost. The 1 unlocked sheet duplicates their unique reference (model no's) once again showing their unique retail cost. I have already added to the unlocked page additional columns (1 with the discount level and another showing therefore our cost after the discount level has been applied and this works fine, no problem)
I am trying to achieve;
(and this is already present as a usual function of excel) Once i have located the product item in the unique tabs pages, i can select & copy the model no. (Ctrl C) listed, search for it elsewhere (Via Ctrl F) (enter the model No.) (search the workbook) and it takes me to the only other reference of the model which is on the unlocked page which already then shows / usual price / discount / discounted cost.
I want the Ctrl F search result (and it jumps to the (only other) reference to the model no. (on the unlocked tab)) to highlight the cell with the model number and also the discounted price in th end column. (ie the £2.60 below)

I have so far got the search result box to go green via vba code (and this was learned from a u tube video !!!) but am now stuck in also getting the discounted Cost to also go green. (i.e. the £2.60) Please help.
VBA code on this page so far to get to the above is
-----------------------------
Option Explicit

Public rngOldCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If rngOldCell Is Nothing Then
Set rngOldCell = Target
Exit Sub
End If

ActiveCell.Select
Selection.Interior.Color = vbGreen
rngOldCell.Interior.Pattern = xlNone

Set rngOldCell = Target

End Sub
-----------------------------
Any assistance or even alternatives would be greatly appreciated.

Many Thanks. NH

 

[Alex Blakenburg]

Not really understanding what your process is but in terms of making the Cost (discounted) green try adding this:
(cell 5 columns to the right)

Selection.offset(,5).Interior.Color = vbGreen

 

[Neil Harrison]

Not really understanding what your process is but in terms of making the Cost (discounted) green try adding this:
(cell 5 columns to the right)

Selection.offset(,5).Interior.Color = vbGreen

Not really understanding what your process is but in terms of making the Cost (discounted) green try adding this:
(cell 5 columns to the right)

Selection.offset(,5).Interior.Color = vbGreen

Alex,

thanks for the reply and thats great after the search it also highlights the cell 5 columns to the right.

1 minor issue when i then select something else on the sheet the prior selected cell still stays green and deosnt return to its oroiginal colour. How do i make that code only apply to the current selection and otherwise return to the normal colour ?

 

[Alex Blakenburg]

Try:= this before changing rngOldCell

rngOldCell.Interior.Pattern = xlNone
rngOldCell.Offset(,5).Interior.Pattern = xlNone

Set rngOldCell = Target

 

[Neil Harrison]

Alex,

thanks for your assistance.

The Joys of excel

 

[Alex Blakenburg]

See how you go but I think you might need something more like this.

Public rngOldCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

Application.EnableEvents = False
If Not Intersect(Target, Columns("B")) Is Nothing And Target.Count Then
    Target.Interior.Color = vbGreen
    Target.Offset(, 5).Interior.Color = vbGreen

    If Not rngOldCell Is Nothing Then
        rngOldCell.Interior.Pattern = xlNone
        rngOldCell.Offset(, 5).Interior.Pattern = xlNone
    End If

    Set rngOldCell = Target
Else
    If Not rngOldCell Is Nothing Then
        rngOldCell.Interior.Pattern = xlNone
        rngOldCell.Offset(, 5).Interior.Pattern = xlNone
    End If
End If
Application.EnableEvents = True

End Sub