Can you please share your file?
On Thu, Jan 30, 2014 at 5:13 AM, Scott Little <scott.lit...@sbdinc.com>wrote:
> 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.
>
--
Regards,
Anoop
Sr. Developer
Facebook ID - https://www.facebook.com/anooop.k.sharma
--
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.
