Highlight to Text

[gueddes]

I have a long spreadsheet that does comparisons, if the comparisons are true column K is highlighted yellow. what im trying to do is then change the highlighted cell to a test that reads "Already Monitored" using VBA. the rest of the cells should stay blank.
Any ideas on this?

thanks

 

[My Aswer Is This]

Give us more details.

If this happens I want this to happen.
And do you want this to happen automatically or when you press a button or manually do something.

We need column numbers and maybe sheet names. We always need exact details.

 

[gueddes]

Ok,
Sheet name for now is Test, it is a monitor for Aircraft faults. Column A is ACFT (aircraft), column B is ATA codes, 4 digit numbers. If Column A and B are reproduced Column K currently turns yellow, that's just how Ive got it coded. I don't need it to be yellow, I have it set to highlight to yellow because Ive brought in data from another sheet that goes below the current data. the data that comes in has the items that are "Monitored". so the point of all this is for those folks that are using this to not work something that has already being worked. So if duplicates show up row K changes to a yellow fill and Im hoping to change the yellow fill to "Already Monitored"

I hope that makes sense

Thx

 

[My Aswer Is This]

What does this mean:
If Column A and B are reproduced
"reproduced"

Does this mean "are exactly the same"

 

[My Aswer Is This]

If reproduced means exactly the same then try this:

Sub Check_Reproduced()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, "K").Value = "Already Monitored"
    Next
Application.ScreenUpdating = True
End Sub

 

[gueddes]

Yes, example is when we do a days run and column A and B are already there, (ex A2 is "5TR" and B2 is "2150") and it shows up again in A and B 20 then Column K2 turns yellow, But K20 has "Monitored" there already and the condition monitoring has already made it an amber color.
I tried the coding, I already have coding in there that checks for duplicates and turns the new duplicates, K2 in the example above, yellow. Im just hoping to change the yellow to the text

Thx

 

[gueddes]

The coding that I used changes the new Column K to yellow but doesn't change the old ones yellow,
Sub ColorDuplicatRowsWorking()

'This code will make row K highlighted in yellow, works if the column is unfiltered
Dim d As Object, a, u, i As Long, n As Long

a = Range("a1").CurrentRegion.Resize(, 2)
n = UBound(a)
Set d = CreateObject("scripting.dictionary")
For i = 1 To n
u = Join(Array(a(i, 1), a(i, 2)), Chr(29))
d(u) = d(u) + 1
Next i
For i = 1 To n
If d(Join(Array(a(i, 1), a(i, 2)), Chr(29))) > 1 Then _
Cells(i, 11).Resize(, 1).Interior.Color = vbYellow
Next i
'IF
End Sub

 

[Akuini]

Hi, gueddes
Maybe this:

[COLOR=blue]Sub[/COLOR] a1015793a[B]()[/B]
 
    Application.ReplaceFormat.Interior.Color [B]=[/B] xlNone
    Application.FindFormat.Interior.Color [B]=[/B] vbYellow
 
    Range[B]([/B][COLOR=brown]"K1"[/COLOR][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [COLOR=brown]"K"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B])).[/B]Replace What[B]:=[/B][COLOR=brown]"*"[/COLOR][B],[/B] _
    Replacement[B]:=[/B][COLOR=brown]"Already Monitored"[/COLOR][B],[/B] lookat[B]:=[/B]xlWhole[B],[/B] searchFormat[B]:=[/B][COLOR=blue]True[/COLOR][B],[/B] ReplaceFormat[B]:=[/B][COLOR=blue]True[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[gueddes]

Thanks Akuiki
its so close, So row K has some cells highlighted yellow which is the days run, then imported at the bottom are cells that are "Monitored" The coding changed the cells at the bottom to "Already Monitored". These are the Cells that are duplicates. the reason I did it this way was when I change the color to yellow of duplicated cells the yellow wont override the conditional Formatting of the Monitored cells. So I have row K that's blank until the Monitored shows up. then I run the code to find duplicates which highlights in yellow (if its blank) anything that is duplicated and the Monitored is left alone.

Again I appreciate your time

 

[Akuini]

Sorry, actually I don’t quite understand your explanation.
Can you explain it by using some data sample?