Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Color range of cells based upon value in another cell Results

In a Pivot Table I have a column showing Current Age, 1-37, for a number of different Rooms across the top, A-ZZ, and I want to be able to look at the value in Current Age column for a particular row and have it auto fill in twelve cells of that row a certain color based upon the age.

Say if the Current Age is 1 in Row 44, I want to have the values in E44:T44 shaded yellow. If the current Age is 13 in Row 32, I want to have the values in E32:532 shaded green. How might I accomplish this?

Current Age RoomA Room B Room C, etc
Row 32 13 0 6 10 (want this shaded green because Age is 13)
Row 44 1 3 9 5 (want this shaded yellow because Age is 1)

Does this make sense? Any help would be appreciated. Thanks.

Kalli

I have this bit of code that works great to add a checkmark to a cell upon clicking once. However, I would like it to exclude the first row so it doesnt change my header and also exclude conditional formatting I have already set to color the row based on entries in another column. Preserving the conditional formatting is secondary to having the macro ignore the first row. Suggestions appreciated.

Here is the code:

	VB:
	
CODE HERE 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    Dim  cellrow As Integer 
     
    On Error Resume Next 
    If Target.Column = 1  Then 
        cellrow = Target.Row 
        With Range("A" & cellrow) 
            If .Value = "ü" Then 
                .Clear 
            Else 
                .Value = "ü" 
                .Font.Name = "Wingdings" 
            End If 
        End With 
    End If 
    Range("B" & cellrow).Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I am running into a problem specifying a mail merge range in Excel 2002/2003 that only includes non-blank cells. In Excel/Word 2000 I was able to achieve this by hiding unused rows in Excel before performing the mail merge. This also added simplicity to the Excel “form” by showing the user only rows needing to be filled out at the time. In Excel/Word 2002 and beyond, this no longer seems to work, however, one peculiarity seems to make me thing perhaps Excel 2002 and beyond is still capable of limiting a mail merge data source range in this way.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

The actual mail merge is being performed using Microsoft Word VBA code (as I ran into far too many problems in executing the mail merge in Excel) while the Excel VBA code is used for showing/hiding cells, clearing the form, determining the number of printed pages in the final step, and saving and continuing the next step in the mail merge process.

Full details to follow and I’d be more than happy to upload screen captures if that would be of help. If you believe this to be a Word VBA solution I will try and find a Word coding forum in which to ask this question.

Thanks much!

-------------------------------------------------------------------------------------

A year ago I came up with an automation “mini-app” that staff can use to generate the correct number of student aide tags needed for the student aides helping them in their classes. This automation uses a batch script, Excel VBA and Word VBA to complete a mail merge in Microsoft Word.

The batch file copies the Excel and Word documents to the user’s Desktop, launches Excel first, inviting staff to fill out the required columns “TEACHER’S LAST NAME,” “STUDENT’S FULL NAME,” “PERIOD # (INCLUDING SUFFIX),” and “TYPE OF TAG.” There is a textbox in column F that allows users to show or hide cells (eight cells are shown by default), a button to clear the form, a button to find out how many printed pages they should expect (based on a mathematical formula that divides the number of shown cells by eight and another formula that divides the number of filled cells by eight) and a button that saves and closes the Excel document.

Upon clicking the “Save, Close and Continue” button in the Excel spreadsheet, the batch script launches the Microsoft Word document on the Desktop where Word VBA code automatically completes the mail merge with the Excel document on the user’s Desktop and initiates the printing of the completed merged document to the default printer, closes the Word mail merge template document and (upon closure of the Word application) deletes the temporary documents off the user’s Desktop.

The version of Microsoft Word used in our building prior to the upcoming school year was Microsoft Office 2000 (the OS is Windows XP). However, we recently upgraded all of our licenses to Microsoft Office 2003 and I soon realized that I also needed to make a few adjustments to my “mini-app” in order for it to work with the upgraded version of Office.

Everything is now working as it should except for one interesting little bug. In column F of the Excel spreadsheet I added a textbox with some VBA code to hide/show rows in the spreadsheet. By default, only nine rows (eight plus the header row in row 1) show as there are eight student aide tags to a printed page once the mail merge has been completed in Word. The instructors can use the textbox to hide or show additional rows as they need to. This works to simplify the user’s view by allowing them to only show the number of rows needed but also, in Excel/Word 2000, creates a situation where (by default) Word only performs a mail merge against the unhidden cells. This limits the number of pages printed and eliminates the unnecessary printing of blank pages following the completed mail merge page(s).

The rub is this. In Excel 2000 hiding unused rows eliminates the blank pages from being generated in Word by limiting the data source range. However, in Excel 2002 and beyond, hiding rows does not limit the data source range in Word and a number of blank pages (based upon the number of rows unhidden in the first place) are generated following the final mail merge. It doesn’t seem to make a difference if I manually rehide the rows or rehide these using the VBA code associated with the textbox.

For example, the Excel document starts with only nine rows unhidden, and if I precede without unhiding any additional rows only one printed page is generated in Microsoft Word. However, if I unhide say the first 50 rows and then, prior to saving and continuing, decide to rehide all but the first nine rows, six additional blank pages are still generated in Microsoft Word upon the completion of the mail merge. Blank pages occur when unhiding more rows than the user fills out.

What I find interesting about this is that there has to be something saved in the Excel 2003 document upon hiding/unhiding rows. I can temporarily rectify the additional blank pages problem by copying the original, untouched, Excel document and reapplying the mail merge. So I’d deduce that there must be something saved in the original, untouched, Excel 2003 document that allowed Word to limit the data source range to only the non-hidden cells; There must be something that changes in the Excel document when I unhide/rehide cells? However, I have not yet been able to duplicate the results.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

Thanks much!

-Matt-

----------------------------------------------

Here is the VBA code from the Microsoft Excel file:

Excel - Sheet 1 (Generate Aide Tags App):

Option Explicit
Private varNumCellsToShow
Public varReturnDetected

Private Sub btnHowManyPrintedPgs_Click()
Call HowManyPrintedPgs
End Sub

Private Sub btnNumStudsEnter_Click()
On Error Resume Next
Application.ScreenUpdating = False
varNumCellsToShow = txtNumCellsToShow.Text + 2
If txtNumCellsToShow.Text = 65535 Then
MsgBox ("You entered a number greater than or equal to 65535. Showing all 65535 rows.")
txtNumCellsToShow.Text = 65535
varNumCellsToShow = 65536
Range("B1").Select
End If
If varNumCellsToShow = "" Then
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
Rows(varNumCellsToShow & ":65536").Select
Selection.EntireRow.Hidden = True
varNumCellsToShow = ""
End Sub

Private Sub btnSaveAndClose_Click()
ActiveWorkbook.Save
Excel.Application.Quit
End Sub

Private Sub bttnClear_Click()
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-33
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
End Sub

Private Sub txtNumCellsToShow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
varReturnDetected = 1
Call btnNumStudsEnter_Click
End If
End Sub

Private Sub txtNumCellsToShow_LostFocus()
If varReturnDetected 1 Then
Call btnNumStudsEnter_Click
End If
varReturnDetected = 0
End Sub

Excel - Module 1:

Option Explicit
Private mycount As Long

Sub HowManyPrintedPgs()
Dim FinalRow
Dim LastFilledRow
Dim NumberofPages As Integer
Call NumberUnhiddenBlank
If mycount = 0 Then
FinalRow = (Range("B65536").End(xlUp).Row)
If FinalRow 7 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 student aide tags to a page and you have elected to print " & FinalRow - 1 & " tags. Tags will print to " & NumberofPages & " pages.")
End If
End If
If mycount > 0 Then
FinalRow = (Range("B65536").End(xlUp).Row) + mycount
LastFilledRow = (Range("B65536").End(xlUp).Row)
If FinalRow 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tags. IMPORTANT NOTE: You have " & mycount & " blank rows at the bottom of your form. To reduce the number of pages printed, please be sure to adjust your Number of Tags to Create textbox settings. Tags will print to " & NumberofPages & " pages.")
End If
End If
End Sub

Sub NumberUnhiddenBlank()
Dim c As Range
mycount = 0
For Each c In Range("B1:B65536")
If Rows(c.Row).Hidden = False And c.Value = "" Then mycount = mycount + 1
Next c
'MsgBox ("The number of unhidden, blank, rows is " & mycount & ".")
End Sub

Here is the VBA code from the Microsoft Word file:

Option Explicit

Private Sub ActivateMailMerge_Click()
Call Document_Open
End Sub

Private Sub Document_Open()
Dim UserName
UserName = Environ("username")
MsgBox ("This step will take a couple of seconds. Please hang on...")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:Documents and Settings" & UserName & "DesktopStudent Aides.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

Dim NumPgs As Long

NumPgs = Selection.Information(wdNumberOfPagesInDocument)

If NumPgs < 2 Then
Select Case MsgBox("Please insert " & NumPgs & " page of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

If NumPgs >= 2 Then
Select Case MsgBox("Please insert " & NumPgs & " pages of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

End Sub

The title says it all. I have a cell range with 5 possible entries and need the backgrounds color coded to these entries. Some entries are dependant on other entries, so the change of one cell would effect others. It is very simple in design:

P = Yellow
T = Green
X = Gray
F = Dark Green
R = Red

For individual modification I have it set-up with:

	VB:
	
 Range) 
    Dim icolor As Integer 
    If Not Intersect(Target, Range("E4:Q37, S4:W37, Y4:AF37, AH4:AO37")) Is Nothing Then 
        Select Case Target 
        Case "R", "r" 
            fcolor = 1 
            icolor = 3 
        Case "F", "f" 
            fcolor = 2 
            icolor = 10 
        Case "T", "t" 
            fcolor = 1 
            icolor = 43 
        Case "P", "p" 
            fcolor = 1 
            icolor = 6 
        Case "X", "x" 
            fcolor = 2 
            icolor = 16 
        Case Else 
            fcolor = 1 
            icolor = 0 
        End Select 
        Target.Value = UCase(Target.Value) 
        Target.Font.Bold = True 
        Target.Font.ColorIndex = fcolor 
        Target.Interior.ColorIndex = icolor 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I enter a P in one cell it will change another cell to a T, requiring both cells to be recolored, but this script only seems to do one cell. To make it simple, I'd like it to update the entire cell range based upon the individual cell contents whenever any single cell is modified.

Any ideas?

Hi!

I'm a bit of a newbie to VB coding so hopefully this is something which is an easy fix.

I'm currently creating a spreadsheet which has a main sheet which contains every outstanding "job" pulled from an online system. I then have three sheets (Bases, Cabs and Power) that have this information copied directly over from the main sheet. These sheets then use a macro that looks if anything is entered in column N (e.g. a date) which means the job is complete and it can be moved to the complete sheet - (I have managed to do this). The problem is that I have more complicated jobs which have two columns to express whether they are complete meaning that it must now check column N and G before it can be moved.

I have managed to find a bit of code from another website. What the code does is...if column N has data the whole row will be copied into the Completed work sheet and deleted from the all jobs sheet. I am currently trying to adapt the code so that it only cuts and pastes the row if both cell N and G have data.

Here is the current script (sorry about all the notes):


	VB:
	
[FONT=Arial][SIZE=2][COLOR=#000000]Sub ShowCompForBase() 
 
 
 
 ' This macro will move any and all rows from the current active Task Sheet to a
 
 ' Completed Items worksheet based on whether a specific cell in the row is not blank.
 
 ' For example, lets assume column N is where you would enter the date on which
 
 ' a task was completed.  When you run this macro it will detect
 
 ' that there is data in that column and the entire row will be moved to the
 
 ' Completed Tasks worksheet.  This macro looks specifically in column N but
 
 ' this can be modified to look at any column.
 
 
 
 ' The macro can also handle multiple Tasks sheet where completed items
 
 ' are to be moved to their associated Completed Items sheet or to a common
 
 ' Completed Items sheet.  For this purpose I have set up a condition to
 
 ' examine three differnt task sheets "Tasks A", "Tasks B" and "Tasks C".
 
 ' Upon runing the macro, any completed items will be moved from the active
 
 ' Tasks sheet to its corresponding Completed Tasks sheet, A, B or C.
 
 ' It will not, however, do all of the sheets simultaneously.  It will only
 
 ' work on the active Tasks sheet.
 
 
 
 ' FIRST, LETS CREATE A FEW VARIABLES.
 
 
 
 ' Sheet1 is a "String" variable to store the name of the active "Tasks" sheet at the
 
 ' time this macro was launched
 
Dim Sheet1 As String 
 
 
 
 ' Sheet2 is a "String" variable to store the name of the "Completed Tasks" sheet that
 
 '   corresponds with Sheet1
 
Dim Sheet2 As String 
 
 
 
 ' MyRange is a "Range" variable to store the range of rows to be examined in
 
 ' the current sheet.
 
Dim MyRange1 As Range 
 
 
 
 ' MyRange1 is a "Range" variable to store the range of rows to be moved.
 
Dim MyRange As Range 
 
 
 
 ' STEP 1: Store the name of the active Tasks sheet into variable Sheet1
 
 
 
Sheet1 = ActiveSheet.Name 
 
 
 
 ' STEP 2: Based on which Tasks sheet is active we store the name of its corresonding
 
 ' Completed Tasks worksheet in variable Sheet2
 
If Sheet1 = "Bases" Then 
     
    Sheet2 = "Completed Bases" 
     
ElseIf Sheet1 = "Cabs" Then 
     
    Sheet2 = "Completed Cabs" 
     
ElseIf Sheet1 = "Power" Then 
     
    Sheet2 = "Completed Power" 
     
Else 
     
     ' If the macro was activated from any other sheet then we stop running the script
     
    Exit Sub 
     
End If 
 
 
 
 ' STEP 2: Find the last populated row in Sheet1 based on there being data in column A
 
 ' If column A is not a constant in your Tasks sheet then select a column that will
 
 ' always have data in it such as the Task Name or Task Description column.
 
lastrow = Sheets(Sheet1).Cells(Rows.Count, "A").End(xlUp).Row 
 
 
 
 ' STEP 3: Find all rows in Sheet1 in which column N contains data. The macro assumes
 
 ' that you are using column headers therefore it ignores row 1 and starts at row 2.
 
 ' Be sure that your Completed Tasks sheets already have the same headers set up.
 
Set MyRange = Sheets(Sheet1).Range("N2:N" & lastrow) 
 
For Each c In MyRange 
     
    If c.Value  "" Then 
         
        If MyRange1 Is Nothing Then 
             
            Set MyRange1 = c.EntireRow 
             
        Else 
             
            Set MyRange1 = Union(MyRange1, c.EntireRow) 
             
        End If 
         
    End If 
     
Next 
 
 
 
 ' STEP 4: Move rows found in STEP 3 (if any) to the corresponding Completed Tasks sheet
 
If Not MyRange1 Is Nothing Then 
     
    MyRange1.Copy 
     
    Sheets(Sheet2).Select 
     
    lastrow = Sheets(Sheet2).Cells(Rows.Count, "A").End(xlUp).Row 
     
    Sheets(Sheet2).Range("A" & lastrow + 1).Select 
     
    ActiveSheet.Paste 
     
     ' Now we delete the selected rows from Sheet1
     
     ' Note: If the sheet is protected this will cause an error and the macro will terminate
     
    MyRange1.Delete 
     
End If 
 
 
 
 ' Finally we return to the worksheet from which this macro was Launched.
 
Sheets(Sheet1).Select 
 
 
 
End Sub [/COLOR][/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I presumed something quite basic like:

Set MyRange = Sheets(Sheet1).Range("N2:N" & lastrow)

may need to be changed to:

Set MyRange = Sheets(Sheet1).Range("N2:N,G2,G" & lastrow)

But this didn't seem to work and after many hours of playing around I can't understand it

Many thanks in advance!

Chris

Hello all, I am currently operating a small business and can't afford to buy invoice software so I am taking it upon myself to build my own invoice worksheet in excel 2007. I've succeeded to a certain extent but my problem is that I need to differentiate between "C.O.D" and "Credit" bills.
Now I have a drop-down menu created in cell "J15" that speicifies either "C.O.D" or "Credit". Currently my background color for the cell ranges "F17:O19" and "L6:O6" is RGB(149, 55, 53). I would like to know how to make these ranges change to the color RGB(200, 160, 35), when "Credit" is selected from the drop-down menu in cell "J15".
You guys do alot of good work and I appreciate the fact that there are people out there willing to help others. Thank you in advance.

As Stated I am using: MS Excel 2007
Synopsis: Set specified cells background color based on text value of another cell


No luck finding an answer? You could always try Google.