Hello,
I have been working on a VBA macro that is taking data entered from one
sheet and matching to a column on another sheet used as a database. The
matching process works fine, I can get the data entered to match in the
database and highlight the font so it is distinguishable. The issue is that
for the next round of entered data, I want the macro to skip the colored
font. I have tried Offset, hiding the cells, deleting them, etc. but to no
avail. I have a second macro that resets all of the matched values back to
normal so at the end it can start again. Here is my VBA code; the “rng5” is
where I am trying to figure out to skip over the colored fonts i.e. color
index=3. I put in an Else count to verify it is counting the colored or
black bells-depending on the color index. Any suggestions would be
appreciated.
PS I also posted this on Linked in Excel Developers.
Thanks,
Sub Skip_Fonts()
Dim rng1 As Range, c As Range, cfind As Range, rng2 As Range, rng3 As
Range, d As Range, dfind As Range, rng4 As Range, e As Range, rng5 As
Range, s As Double, n As Long
Application.ScreenUpdating = True
Worksheets("User").Activate
'searches for matching stage lanes from Gui
Set rng1 = Range(Range("i13"), Range("i20").End(xlUp))
Worksheets("Dbase").Activate
Set rng2 = Range(Range("C2"), Range("C500").End(xlUp))
'Finds matches then highlites in Dbase
For Each c In rng2
If IsEmpty(cell) Then
Set cfind = rng1.Cells.Find(what:=c.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then c.Font.ColorIndex = 3
End If
Next c
'searches for matching stage lanes from WMS report
Worksheets("Dbase").Activate
Set rng3 = Range(Range("N2"), Range("N500").End(xlUp))
Set rng4 = Range(Range("C2"), Range("C500").End(xlUp))
'Finds matches then highlites in Dbase
For Each d In rng4
If IsEmpty(cell) Then
Set dfind = rng3.Cells.Find(what:=d.Value, lookat:=xlWhole)
If Not dfind Is Nothing Then d.Font.ColorIndex = 3
End If
Next d
Set rng5 = Range(Range("C2"), Range("C500").End(xlUp))
For Each e In rng5 'set the desired range
If (e.Font.ColorIndex = 3) _
Then 'use the matched stage lane
‘Here is the matching formula on the Gui-this what I want to skip
to next value if colored font
'Sheets("User").Range("e13").Value =
Evaluate("=INDEX(Dbase!c2:c500,MATCH(E11,Dbase!a$2:a$500,0))")
‘e.Offset(1, 0) = e; tried using offset but would only add to
existing cells.
‘what else would work to match the cells '.Delete shift:=xlUp
Else
n = n + 1
'skip the stage if e.Font.ColorIndex = 1; it is black font
End If
Next e
If n > 0 Then
MsgBox "black cells=" & n
Else
MsgBox "no black cells"
End If
Worksheets("User").Activate
Application.ScreenUpdating = False
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
