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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
1. Can you confirm whether A1:T27 contains any formulas or not? If so, which cells?

2. Are the values like 08 text "08" or just the number 8 with custom formatting to show the leading zero?
 
Last edited:
Upvote 0
Peter_SSs said:
1. Can you confirm whether A1:T27 contains any formulas or not? If so, which cells?

2. Are the values like 08 text "08" or just the number 8 with custom formatting to show the leading zero?
Click to expand...


1. No, there is not any formulas. I am doing it manually

2. the values like 08 Number "08"
 
Last edited:
Upvote 0
Dsunil05 said:
2. the values like 08 Number "08"
Click to expand...
That is not clear to me. In your image, cell A2 shows "08" If you go to a blank cell and put this formula, what does it return?
=ISNUMBER(A2)
 
Upvote 0
Dsunil05 said:
It returns "TRUE"
Click to expand...
OK, try this macro on a copy of your workbook.
Code: 
Sub MarkMatches()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim Clr As Long, i As Long, j As Long, rws As Long, cols As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Application.ScreenUpdating = False
  With Range("A1:T27")
    Clr = .Cells(.Rows.Count, 1).Font.Color
    a = .Value
    rws = UBound(a, 1)
    cols = UBound(a, 2)
    ReDim b(1 To rws - 1, 1 To cols)
    For j = 1 To cols
      d(a(rws, j)) = 1
    Next j
    For i = 1 To rws - 1
      For j = 1 To cols
        If d(a(i, j)) = 1 Then b(i, j) = 1
      Next j
    Next i
    .Font.Color = 0
    .Value = b
    .SpecialCells(xlConstants).Font.Color = Clr
    .Value = a
  End With
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
not working
same no of range A27:T27 in Between rang A1:T26 number color not change
not finding duplicate
 
Upvote 0
Peter_SSs said:
OK, try this macro on a copy of your workbook.
Code: 
Sub MarkMatches()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim Clr As Long, i As Long, j As Long, rws As Long, cols As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Application.ScreenUpdating = False
  With Range("A1:T27")
    Clr = .Cells(.Rows.Count, 1).Font.Color
    a = .Value
    rws = UBound(a, 1)
    cols = UBound(a, 2)
    ReDim b(1 To rws - 1, 1 To cols)
    For j = 1 To cols
      d(a(rws, j)) = 1
    Next j
    For i = 1 To rws - 1
      For j = 1 To cols
        If d(a(i, j)) = 1 Then b(i, j) = 1
      Next j
    Next i
    .Font.Color = 0
    .Value = b
    .SpecialCells(xlConstants).Font.Color = Clr
    .Value = a
  End With
  Application.ScreenUpdating = True
End Sub
Click to expand...
If your interpretation of what the OP wanted is correct, then your macro can be written more compactly (it may even be faster) this way...
Code: 
[table="width: 500"]
[tr]
	[td]Sub MarkMatches()
  Dim C As Long
  Application.ReplaceFormat.Clear
  For C = 1 To 20
    Application.ReplaceFormat.Font.Color = Cells(27, 1).Font.Color
    Range("A1:T26").Replace Cells(27, C), "", xlWhole, searchformat:=False, ReplaceFormat:=True
  Next
End Sub[/td]
[/tr]
[/table]
However, I read the OP's request differently. I took the text snippet "And change their 'cell color and text color'" from his original post to mean the each cell in A27:T27 has a different interior and font color (even though his posted table shows all the interiors/fonts the same) and that he wants those copied into the matching cells. Here is my code for that interpretation of the OP's request...
Code: 
[table="width: 500"]
[tr]
	[td]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
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
If your interpretation of what the OP wanted is correct, then your macro can be written more compactly (it may even be faster) this way...
Code: 
[table="width: 500"]
[tr]
	[td]Sub MarkMatches()
  Dim C As Long
  Application.ReplaceFormat.Clear
  For C = 1 To 20
    Application.ReplaceFormat.Font.Color = Cells(27, 1).Font.Color
    Range("A1:T26").Replace Cells(27, C), "", xlWhole, searchformat:=False, ReplaceFormat:=True
  Next
  [B][COLOR="#FF0000"]Application.ReplaceFormat.Clear[/COLOR][/B]
End Sub[/td]
[/tr]
[/table]
However, I read the OP's request differently. I took the text snippet "And change their 'cell color and text color'" from his original post to mean the each cell in A27:T27 has a different interior and font color (even though his posted table shows all the interiors/fonts the same) and that he wants those copied into the matching cells. Here is my code for that interpretation of the OP's request...
Code: 
[table="width: 500"]
[tr]
	[td]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
  [B][COLOR="#FF0000"]Application.ReplaceFormat.Clear[/COLOR][/B]
End Sub[/td]
[/tr]
[/table]
Click to expand...
I forgot to include the "clean up" code line I show in red above. It is not critical to the operation of the code, rather, it is a kindness to the OP for the next time he uses Excel's Find dialog box. The VBA Replace function and Excel's Find/Replace dialog box share the same search and replace format criteria, so if we don't clean up our code as above, any search and/or replace format criteria we set in our code would be "remembered" by Excel the next time the Find/Replace dialog box is called and, hence, be in effect even if the OP did not realize it.
 
Upvote 0
Rick Rothstein said:
I forgot to include the "clean up" code line I show in red above. It is not critical to the operation of the code, rather, it is a kindness to the OP for the next time he uses Excel's Find dialog box. The VBA Replace function and Excel's Find/Replace dialog box share the same search and replace format criteria, so if we don't clean up our code as above, any search and/or replace format criteria we set in our code would be "remembered" by Excel the next time the Find/Replace dialog box is called and, hence, be in effect even if the OP did not realize it.
Click to expand...

still not working

how post the screen shot of my manual work
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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