Isolating The First Instance of A New Value In A Range

[Ark68]

I've been working on some code that I found some success with. It does some things write, but I think more by accident, than by logic. It certainly isn't very efficient, and is highly inaccurate.

I hope I can explain this properly, part of my frustration is I can't explain it to myself properly.

Consider this sample of data:

Excel 2010
	H	I	J	K	L	M	N	O	P	Q
1	Groom	Prepare	Signature	Lights On	Lights Off	1	2	3	4	Close
2	Kelly	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Mike
3	Kelly	Kelcey	Kelcey			Kelcey	Mike	Kelcey	Kelcey	Kelcey
4	Kelly	Kelcey	Kelcey	Kelcey	Mike					Mike
5	Kelly	Kelcey	Kelcey	Kelcey	Kelcey					Mike
6	NA	Kelcey	Kelcey	Kelcey	Mike					Mike
Sheet57

Assume variable rname = "Mike"

In each row, from left to right of each instance of rname,

a) shade all cells that don't hold rname.
b) shade all the cell values as to obscure them EXCEPT the first value in a series of equal ones.

So ... the processed data above, would look like this:

Excel 2010
	H	I	J	K	L	M	N	O	P	Q	R
8	Groom	Prepare	Signature	Lights On	Lights Off	1	2	3	4	Close
9	Kelly	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Kelcey	Mike	DT
10	Kelly	Kelcey	Kelcey			Kelcey	Mike	Kelcey	Kelcey	Kelcey	DT
11	Kelly	Kelcey	Kelcey	Kelcey	Mike					Mike	DR
12	Kelly	Kelcey	Kelcey	Kelcey	Kelcey					Mike	DR
13	NA	Kelcey	Kelcey	Kelcey	Mike					Mike	DR
Sheet57

Keep in mind, the data is dynamic ... different names and different sized series of equal names

I hope I've given enough information for this to work.

 

[venkat1926]

try this macro

Sub TEST()


Dim nname As String, r As Range, c As Range
nname = InputBox("typethe name other than whichto emarked e.g. Mike")


Set r = Range("H1").CurrentRegion
'MsgBox r.Address
For Each c In r
'c.Select
If c.Value <> nname And c.Column > Range("J1").Column Then
c.NumberFormat = ";;;"


End If
Next c
End Sub

 

[Ark68]

Hi venkat.
I appreciate the effort you put into helping me find a solution to my task. Unfortunately, it didn't produce the results I was looking for.

Consider this example with my worksheet, the first 12 rows are headers.
Row 13 is the row we wish to scour to apply this code.
Only columns I:L need to be examined for the names.

Cells I13 through K13 hold the value "Kelcey"
Cell L13 holds the value "Mike"

The solution I am looking for will search the cells between I13:L13 looking for the first instance of "Mike". Once it's found, the code will look at all the cells to the left of it (to I13) for the next name change not equal to the one directly to it's immediate left.

In this case ... Mike is first found at L13. The value at K13 is "Kelcey". This code will identify (highlight cell) the first instance of "Kelcey" between I13 and L13 LEFT TO RIGHT. The cell that will be therefore highlighted ... I13. It is the first cell in the range I13:J13 that equals K13.

?? Whew! Dizzy.

Your code appears to step through each column from A through ?? and identifies the first cell to the left of "Mike" that isn't equal to "Mike". In my sample ... it's highlighting K13 ... the first instance of "Kelcey", but right to left. It's also highlighting row 12 values, rather than the row with the active data in it. It then proceeds to highlight each cell in each column after column K.

This is the code I was testing with with some diagnostic additions and the line to highlight the cells.

Sub TEST()


Dim nname As String, r As Range, c As Range
nname = InputBox("typethe name other than whichto emarked e.g. Mike")


Set r = Range("I13").CurrentRegion
'MsgBox r.Address
For Each c In r
    'c.Select
    MsgBox "C:  " & c.Value & Chr(13) & "R:  "
    If c.Value <> nname And c.Column > Range("J13").Column Then
        'c.NumberFormat = ";;;"
        c.Interior.ColorIndex = 15
    End If
Next c
End Sub

Thanks again!

Jenn