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

D

Dsunil05

New Member
Joined
Feb 20, 2015
Messages
34
Office Version
  1. 2021
  2. 2019
  3. 2013
  4. 2007
  5. 2003 or older
Platform
  1. Windows
12 [TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD="align: right"]08[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"].
.
.
.
.
.
.
.
.
.
.
.
.[/TD]
[/TR]
[TR]
[TD="align: right"]03[/TD]
[TD="align: right"]09[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]02[/TD]
[TD="align: right"]05[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD="align: right"]03
[/TD]
[TD="align: right"]06[/TD]
[TD="align: right"]08[/TD]
[TD="align: right"]09[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]79[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
Dsunil05 said:
still not working
Click to expand...
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?
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Dsunil05 said:
still not working

how post the screen shot of my manual work
Click to expand...


21687976_1711033845587229_8428108761949864959_n.jpg
 
Last edited:
Upvote 0
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
Click to expand...

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.
 
Last edited:
Upvote 0
Dsunil05 said:
21687976_1711033845587229_8428108761949864959_n.jpg
Click to expand...
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.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...


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
 
Upvote 0
Dsunil05 said:
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
Click to expand...
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.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...

#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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
Edit: The response below was before I had seen post #19 . I'm not sure if that changes anything

Try this
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
conditional formatting question
Replies
2
Views
586
kerm007
K
M
  • Solved
Chose random % of row from sheet1 and paste in to sheet2
Replies
3
Views
240
motilulla
M
togenmoser
  • Solved
Finding all possible combinations of 2 sums
2
Replies
10
Views
718
togenmoser
togenmoser
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
J
  • Question Question
VBA: delete all rows where value (time) is less than 1 minute
2
Replies
13
Views
219
RobP
R

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top