Solved

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

Posted on 2013-11-06
5
334 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
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:DanCh99
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 80

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 80

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now