Find same value and Changing a cell & tex color WITHOUT using Conditional Formatting

[Dsunil05]

12

	19	21	25	29	31	32	33	34	36	37	39	44	50	53	55	66	74	75	77
08	13	16	18	22	23	31	39	45	47	52	59	64	70	71	72	73	74	77	79
																			. . . . . . . . . . . . .
03	09	10	11	13	18	19	27	35	37	44	50	57	59	65	70	72	76	79	80
02	05	11	14	17	28	32	33	37	38	42	47	59	63	64	71	73	74	77	78
03	06	08	09	13	21	24	30	31	36	38	42	49	54	59	65	70	72	75	79

<tbody>
</tbody>

data range A1:T27
i want to find out same number And change their "cell color and text color" in data range A1:T26 of row no 27's 20 number in pink colour.
Without using Conditional Formatting
Because i copy it and use it to another data set in same worksheet e.g. range U1:AO27
In MS Excel 2007
Thanks

 

[Rick Rothstein]

still not working

Strange since the code I posted works fine for me whether the numbers are all text value or real numbers. Do you by any chance have some kind of mixture of real numbers and text numbers? Any chance you can post a copy of your workbook to DropBox or some other such file sharing facility so that we can watch our codes run directly against your actual data?

 

[Dsunil05]

still not working

how post the screen shot of my manual work

 

[Ingolf]

Sub MatchNumbersCopyFormats()
Dim C As Long
Application.ReplaceFormat.Clear
For C = 1 To 20
Application.ReplaceFormat.Interior.Color = Cells(27, C).Interior.Color
Application.ReplaceFormat.Font.Color = Cells(27, C).Font.Color
Range("A1:T26").Replace Cells(27, C), "", xlWhole, SearchFormat:=False, ReplaceFormat:=True
Next
Application.ReplaceFormat.Clear
End Sub

It's working very well.

In what way it is not working???? Explain!!!
Post a copy of your workbook NOT a pictures, to DropBox or some other file sharing.

 

[Rick Rothstein]

That is a picture... I meant a real Excel workbook that was a copy of your physical workbook. I can open a real workbook but I cannot do anything with a picture except look at it. You cannot post a real Excel workbook here which is why I suggested upload it to DropBox or some other file sharing facility.

 

[Dsunil05]

That is a picture... I meant a real Excel workbook that was a copy of your physical workbook. I can open a real workbook but I cannot do anything with a picture except look at it. You cannot post a real Excel workbook here which is why I suggested upload it to DropBox or some other file sharing facility.

https://drive.google.com/file/d/0B1u8QGSQIxdoeXRvRHd2THVaenM/view?usp=sharing



step by step what i am doing for duplicate manualy

1. create table
2. then enter numbers in cells
3. then right click on selected range A1:T27
4. click on format cells
5. in number tab select custom catagory
6. then in "type" text box enter "00" for convert any single number come with double e.g. 1 = 01
7. find duplicate one by one manualy

 

[Ingolf]

In what way it is not working???? Explain!!!
What is wrong?

7. find duplicate one by one manualy

Try to explain.

 

[Rick Rothstein]

https://drive.google.com/file/d/0B1u8QGSQIxdoeXRvRHd2THVaenM/view?usp=sharing

step by step what i am doing for duplicate manualy

1. create table
2. then enter numbers in cells
3. then right click on selected range A1:T27
4. click on format cells
5. in number tab select custom catagory
6. then in "type" text box enter "00" for convert any single number come with double e.g. 1 = 01
7. find duplicate one by one manualy

I am not sure what #7 means, but when I remove the background color and font color for cells A1:T26 and then run my macro, the colors are all reestablished correctly, so when you say my code is not working, I don't know what you mean. You are going to have to tell us in detail how it is not working for you. What is it doing that you do not want to happen? Or, said another way, what is it you expect to happen that isn't happening.

 

[Dsunil05]

I am not sure what #7 means, but when I remove the background color and font color for cells A1:T26 and then run my macro, the colors are all reestablished correctly, so when you say my code is not working, I don't know what you mean. You are going to have to tell us in detail how it is not working for you. What is it doing that you do not want to happen? Or, said another way, what is it you expect to happen that isn't happening.

#7 it means

look at A27 number and find them manually and select in every row by holding ctrl key then change its font color, style "bold" and cell color also

repeat this for every number of row 27

your code not working means

when run the macro there is no change in font color or cell color
of A1:T26 And A27:T27

it is viewing normal

 

[Dsunil05]

so sorry it was my mistake
that before run the macro not change color of cell,font and style of A27:T27
that's why it is not showing result
right
but it is not doing automatically every time do change color of cell,font and style of range e.g.A27:T27

can i repeat this or copy and past it and use it to another data set in same worksheet e.g. range V1:AO27 to XEK1:XFD27 different number of sets

 

[Peter_SSs]

Edit: The response below was before I had seen post #19 . I'm not sure if that changes anything

Try this

Sub MarkMatches_v2()
  Dim a As Variant
  
  Application.ScreenUpdating = False
  With Range("A1:T27")
    a = .Value
    .Font.Bold = True
    .Value = Evaluate(Replace("if(row(#),match(#," & Range("A27:T27").Address(1, 1) & ",0),"""")", "#", .Address))
    With .SpecialCells(xlConstants, xlNumbers)
      .Font.Color = 11851260
      .Interior.Color = 411543
    End With
    .Value = a
  End With
  Application.ScreenUpdating = True
End Sub