Solved

Turn row red, from a value on a different tab.

Posted on 2013-11-06
5
375 Views
Last Modified: 2013-11-07
This file has serviced us for a long time, however we have found a problem that has caused us to look at an edit.  We need to add a procedure that will do the following:

1.      Check column C, of the MasterList tab for any text that is red.

a.      If it finds red text?  (usually in a group of 5,6,7 etc? Chgs by the file, may need to add a column and remove dups before looking for red text).  After finding one grab the value in column A, and then search, column A of the TemplateLayOut and turn that rows text red also.  It will be there in the TemplateLayout tab.  MasterList tab, creates TemplateLayOut tab.

b.      Search the whole column C in of MasterList until all have been checked, leaving the data intake.  MasterList does not have the same amount of rows each time.

c.      If it does not find any red text in column C of the MasterList, exit sub and done.

Some additional notes that may assist are that it is a dynamic extract, number of columns and rows are never the same.. (unless you extract the same material number¿)  Column C, of MasterList tab is a ValidUntil date and these are no longer being used and we need a way to flag those rows to filter them out.

Need a hand writting this script for VBA.
Please advise and thanks. -R-
AddOOE-FlagsInTemplateLayoutPg.xlsx
0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Danny Child
ID: 39629148
Conditional formatting may do this without VBA.
Check Examples 3 and 4 here:
http://www.techrepublic.com/blog/10-things/10-cool-ways-to-use-excels-conditional-formatting-feature/
0
 
LVL 81

Expert Comment

by:byundt
ID: 39629240
Here is a macro that uses an AutoFilter on MasterList column C, looking for red font color. It then adds the values from column A of the visible rows to a Dictionary object. Doing so eliminates any duplicates. It then checks worksheet TemplateLayOut column A for the values in the Dictionary and colors them red.

You didn't say whether you wanted just column A colored red or all the data cells in that row. I show both options in the macro (with comments to mark the statements).
Sub RedFontFinder()
Dim cel As Range, rg As Range, rgFilt As Range, targ As Range
Dim dicRed As Object
Dim i As Long, n As Long
Application.ScreenUpdating = False
Set dicRed = CreateObject("Scripting.Dictionary")
With Worksheets("MasterList")
    Set rg = .Cells(1, 3)       'Cells in column C
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    Set rgFilt = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)
End With
With Worksheets("TemplateLayOut")
    Set targ = .Cells(5, 1)     'Cells in column A
    Set targ = Range(targ, .Cells(.Rows.Count, targ.Column).End(xlUp))
    Set targ = Range(targ.Cells(1, 1), targ.Cells(targ.Rows.Count, .Cells(5, .Columns.Count).End(xlToLeft).Column))
End With
rg.Cells(1, 1).AutoFilter
rg.AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
Set rgFilt = rgFilt.SpecialCells(xlCellTypeVisible)

On Error Resume Next
For Each cel In rgFilt.Cells
    dicRed.Add dicRed.Count, cel.Offset(0, -2).Value
Next

n = dicRed.Count
For i = 0 To n
    Set cel = Nothing
    Set cel = targ.Columns(1).Find(dicRed.Item(i))
    If Not cel Is Nothing Then
        'cel.Font.Color = RGB(255, 0, 0)                             'Color just column A font red
        Intersect(cel.EntireRow, targ).Font.Color = RGB(255, 0, 0)  'Color entire row font color red
    End If
Next
On Error GoTo 0
rg.Cells(1, 1).AutoFilter
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39630075
I attached an error screenshot.  It is failing on:
Set rgFilt = rgFilt.SpecialCells(xlCellTypeVisible)

Not sure why.. please advise and thanks. -R-
RedFontError.jpg
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39630307
I can reproduce your error if the TemplateLayOut worksheet is active, as the wrong column was being filtered on MasterList. The error does not occur if MasterList worksheet is active when macro is launched.

The following code overcomes that issue.
Sub RedFontFinder()
Dim cel As Range, rg As Range, rgFilt As Range, rgx As Range, targ As Range
Dim dicRed As Object
Dim i As Long, n As Long
Application.ScreenUpdating = False
Set dicRed = CreateObject("Scripting.Dictionary")
With Worksheets("MasterList")
    Set rg = .Cells(1, 3)       'Cells in column C
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    Set rgx = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)
End With
With Worksheets("TemplateLayOut")
    Set targ = .Cells(5, 1)     'Cells in column A
    Set targ = Range(targ, .Cells(.Rows.Count, targ.Column).End(xlUp))
    Set targ = Range(targ.Cells(1, 1), targ.Cells(targ.Rows.Count, .Cells(5, .Columns.Count).End(xlToLeft).Column))
End With
rg.Cells(1, 1).AutoFilter
rg.Offset(0, -2).Resize(, 3).AutoFilter Field:=3, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor

On Error Resume Next
Set rgFilt = rgx.SpecialCells(xlCellTypeVisible)
If rgFilt Is Nothing Then
    MsgBox "No cells with red font were found"
    Exit Sub
End If

For Each cel In rgFilt.Cells
    dicRed.Add dicRed.Count, cel.Offset(0, -2).Value
Next

n = dicRed.Count
For i = 0 To n
    Set cel = Nothing
    Set cel = targ.Columns(1).Find(dicRed.Item(i))
    If Not cel Is Nothing Then
        'cel.Font.Color = RGB(255, 0, 0)                             'Color just column A font red
        Intersect(cel.EntireRow, targ).Font.Color = RGB(255, 0, 0)  'Color entire row font color red
    End If
Next
On Error GoTo 0
rg.Cells(1, 1).AutoFilter
End Sub

Open in new window

AddOOE-FlagsInTemplateLayoutPgQ2.xlsm
0
 

Author Closing Comment

by:RWayneH
ID: 39630889
Works great!!  Thanks!  EXCELent!!  -R-
0

Featured Post

Myth Busting: MongoDB Scalability (it scales!)

I was talking with one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. He mentioned to me that several customers have been telling him that “MongoDB doesn’t scale!” MongoDB’s scalability was in question?

My response was, “Is that a joke?"

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question