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

Free Microsoft Excel 2013 Quick Reference

Macro to format columns based on cell value

If I were working on Excel 2007 I think I would know how to do this but since I am on 2003 I am hoping someone can tell me if this can even be done.

Premise:
I have a "Value" Sheet and depending on whether the currency value is USD or GBP I would like Columns W thru BF, BI,BK,BM,BO,BQ,BS,BU to reflect the appropriate symbol.

USD = $
GBP = £

The cell that dictates what the results will be is $I$3

Can this be done?

My VBA skills are improving but as my moniker suggests, I am "Guru in Training" aka "VBA Newbie"!

Also, can anyone tell me why all of a sudden I can't assign Macros to "buttons"? Is there some sort of option that needs to be reset?

All help is greatly appreciated!


Post your answer or comment

comments powered by Disqus
Hi. I have a worksheet that I want to hide columns based upon the value of a cell in the column to be hidden.
i.e. if the value of the cells in range G5:DO5 is = "optl" then hide the column of that cell. Otherwise that column is to be visible.

Thank you!

I'm trying to write a macro that will automatically hide certain columns
based on a cell's value. Basically, if C3 = "Sales" I need columns R, S, and
T to be hiden; however if C3 does not equal "Sales" i need columns L, M and N
to be hiden, but R, S, and T need to come back into view and vice versa. Is
there a macro that can assist me? Please help. Thanks!

Hello,

I have a workbook which contains several sheets that are emailed to individual end users (each user has their own sheet). I have a macro that copies each sheet and emails based on the email within the sheet. What I want to do now is add a password to each created sheet based on a value that is in cell G3. So in the end everyone gets their own data that has a unique password.

Here is the code so far:

Sub Email_Each_Sheet()
'Working in 2000-2010
    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
 
 
For Each sht In ActiveWorkbook.Sheets
If sht.Range("A60").Value Like "?*@?*.?*" Then
sht.Activate
SendTo = sht.Range("A60").Value
 
    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("A2:H46").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, " & _
               "please correct and try again.", vbOKOnly
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wb = ActiveWorkbook
     
 
    Set Dest = Workbooks.Add(xlWBATWorksheet)
    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    
    End With
    TempFilePath = Environ$("temp") & ""
    TempFileName = sht.Range("F3").Value & " Productivity " & Format(Now, "mm-dd-yy")
    If Val(Application.Version) < 12 Then
        'You use Excel 2000-2003
        FileExtStr = ".xls": FileFormatNum = 56
    Else
        'You use Excel 2007-2010
        FileExtStr = ".xls": FileFormatNum = 56
    End If
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .to = SendTo
            .CC = ""
            .BCC = ""
            .Subject = "subject"
            .body = "body"
            .attachments.Add Dest.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:test.txt")
            .Display 'or use .Send
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
    Kill TempFilePath & TempFileName & FileExtStr
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    End If
    Next
End Sub

Thank you!

I'm using column A as a reference.
My data starts in cell A3.

Whenever theres a zero in any cell in column A I would like that row to hide.
If the row was hidden and the value changes to any number greater than zero I need the row to unhide.

I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.

Hi

I'm trying to write a very simple code (not using case select) to unhide columns based on variable entered into cell C5. (It's a number from 1 - 12 to represent a month)

I tried using offset columns based on the month entered (so... if month is March (3)... ) but I'm getting a error message that I can't figure out....

"Unable to set hidden range property of Range Class"... help?!

Sub RunningAverageQualityScore()

Dim iMonth As Integer 'review month
Dim FinalColumn As Integer 'end of chart
Dim FirstColumn As Integer 'beginning of chart
Dim ColumnsHide As Integer 'counts columns to hide


FinalColumn = Range("FinalColumn").Select
FirstColumn = Range("FirstColumn").Select
iMonth = Range("C5").Value
ColumnsHide = -12 + iMonth

Range("FinalColumn").Offset(0, ColumnsHide).Hidden = True



End Sub


Hello again OZgrid,
While keeping in the same row, how can I move cell values of a specific column to a different specific column based on a value in a specified column?

Example:

Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7
_____________________________________________________________
1 4
2 9
3 10
4 12.5
_____________________________________________________________

End of example

Based on the value in Column3 the cell data in column4 would be moved to another column or left in column4
Also, depending on the value in Column3 a letter code might be added to Column6

KEY:
1 = leave cell data value in column4
2 = Move cell data in column4 to column5
3 = Move cell data in column4 to column7 and add Letter code ( J ) to column 6
4 = Move cell data in column4 to column7 and add letter code ( V ) to column 6

I have more but if I can find a basic example for moving cell values based on the type of criteria here, I should be able to put it together.

Thanks for you valuable help.

John

I'd like to copy an existing workbook (that's closed, preferably) and name
the copy based on cell values. So, for example in column A of the active
workbook I have a list of different excel workbooks that are closed (with the
file path), and in column B I have a list of names that I want to call the
copied workbooks. For all of the items in the list I'd like to copy the
respective workbook and give it the listed name. I figure I need a VBA loop,
but have no idea on the commands needed. Thanks.

Hi,
Need to insert 2 blank rows and sum columns (Columns B & D; range varies) based on cell value change (Column A).
Regards,
Pete

I cannot get my macro to hide rows based on the value of a cell linked to a check box. I got a macro to work when typing directly into a cell, but cannot get it to hide rows when a cell value is updated based on whether a check box is checked or not. So, if the check box is checked, the value in the linked cell becomes TRUE. Here's the code I used to hide a range of rows when typing the word TRUE directly in a cell:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
     Rows("6:7").Hidden = Application.CountIf(Range("A3"), "TRUE")
End Sub
Can this be modified to work with a cell value based on a formula? I'm a novice with VBA, so any help is appreciated.

Thanks.

Hi

If I use the following formula:

=INDEX(B:B,A1)

it gives me the value contained in column B at the row number defined by the value in A1. What I need is a formula where I can define the column letter based on the value of a cell. For example:

=INDEX(A2:A2,A1)

I know using INDIRECT solves this, but it is a volitile function and slows down my spreadsheet. Is there another way.

Doug

Hi I am hoping someone can help me,

I have a "Course Builder" sheet which people enter course codes onto, i have the macro to move the course info to a "Total Library" sheet and clear the data from the course builder sheet. What i would like to do now is have a macro/code that searches the "Total Library" sheet and copys a row from column H:R to another sheet based on the value in column C. For example column C value is "NRDC_" this row moves to a sheet named "NRDC", i have 11 different sheets to move info into. I would need to paste it into the next available row but not copy a course code if it already exists on the target sheet.
I can do this with a macro by filtering the data on Total Library for all 11 sheets and copy and paste that way, i was just wondering if anyone new of a smarter/quicker way of doing it?

Any help would be gratefully received.

Irleand316

hello,

I am a new member to this site and found it very useful. However I have been searching the forums and trying to learn how to edit these codes all weekend. I have a worksheet that needs to be sorted then moved to another sheet based on a value. I've tried serveral codes on this site but none seems to work. I have 200+ books that I need to sort and moved. The value is from column F. For every unique item I have to put that into a seperate sheet with like items.

attached is a sample. Its an autocad points ifle. I have to seperate each point based on what type of object it is. Once the sheet is created i have to save sheet as a comma delimited text so I can import it into autocad.

I have a workbook where I want it to print worksheets based on cell value. I always want the main worksheet (called Template) to print. I also want the worksheet associated with the value in cell A1:J1 (merged cell) to print. I thought I had the code right, or pretty close, but it doesn't seem to be working.

Public Sub PrintReport()
     
    Worksheets("Template").Select
     
    If Range("A1:J1").Value = "Example 1" Then
        Worksheets("Template").PrintOut
        Worksheets("Example 1").PrintOut
         
       If Range("A1:J1").Value = "Example 2" Then
        Worksheets("Template").PrintOut
        Worksheets("Example 2").PrintOut
             
        If Range("A1:J1").Value = "Example 3" Then
          Worksheets("Template").PrintOut
          Worksheets("Example 3").PrintOut
                
            End If
        End If
    End If
     
     End Sub
Is there something I'm missing here? Also, I want the Print Options pop-up box to appear as we sometimes need to change things there.

In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).

Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)

Range U28:U46 will assume the name of sheet2A11 & sheet2A4.

Range W28:W46 will assume the name of sheet2A11 & sheet2A5

etc.

Right now I am calling the code when something is entered into A11.

I have tried if statement and select case, but I ran into complications with both.

I have posted both codes with the questions I have concerning those codes.

Can you help me either use one of these codes or come up with a better way?

SELECT CASE METHOD

Select Case
Target.Address

    Case "$A$ll"
        Sheet11.Range("S28:S46").Name = Target & Range("A3")
        Sheet11.Range("U28:U46").Name = Target & Range("A4")'
        Sheet11.Range("W28:W46").Name = Target & Range("A5")
        Sheet11.Range("Y28:Y46").Name = Target & Range("A6")
        Sheet11.Range("AA28:AA46").Name = Target & Range("A7")
    
    Case "$A$32"
        Sheet11.Range("S49:S54").Name = Range("A3") & Target
    
End Select
Nothing happens with this code, and I know it is because I do not have a value assigned to A11, but I have no idea what the user will enter into A11. I just need all those named ranges to occur once something is entered into A11.

IF STATEMENT METHOD

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$A$11" Then Sheet11.Range("S28:S46").Name = Target &
Range("A3")
This code returns the desired result; however, I need to add to it, and I am not sure how. Something being entered into A11 results in 4 name codes. With an if statement I do not know how to display multiple results, which is why I tried the select case method

Move cell value to left column based on Criteria

Hi,

I am trying to do the following and have tried Hlookup with If statement but can't get this to work. I Input 1 value (say 3000) in any cell between Column k37:V37, I would like this number to enter in same row to corresponding columns AA37:AL37 based on following Criteria in Column X37 if X37 =7 (then enter K37 to AA37) like for like - apr -apr if X37 =30 (then enter K37 to AB37) Apr(k37) to May(AB37) if X37 =60 (then enter K37 to AC37) Apr(k37 to Jun(AC37) 

Any help would be much appreciated. I'm hoping to do this with a macro if possible.

Many thanks in advance

Haz

I am using the code below to format chart borders based on cell values. There are 32 charts embedded in the "charts" sheet and 32 rows of data in the "data" sheet. The charts are named "1" through "32". My hope is to format chart "1" based on the data in row 1, and so on. The macro is formatting the charts. However, not in the appropriate order. I have 8 rows of charts with 4 charts on each row. They are ordered by name from left to right, top to bottom. When I run the macro, it skips the 4th chart in each row (4, 8, 12, etc.) and returns to format them as charts 25 - 32. I had to delete those charts and create new ones at one point due to some formatting issues, so that may have something to do with it. Is there a way to make this work?


	VB:
	
 GraphLoop1() 
     
     
    For x = 1 To 32 
         
        Sheets("Data").Select 
        If Cells(x, 11).Value = "" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = none 
            End With 
        End If 
        Sheets("Data").Select 
        If Cells(x, 11).Value = "R" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = x1Solid 
                .ChartArea.Border.Weight = 4 
                .ChartArea.Border.Color = RGB(192, 0, 0) 
            End With 
        End If 
        Sheets("Data").Select 
        If Cells(x, 11).Value = "Y" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = x1Solid 
                .ChartArea.Border.Weight = 4 
                .ChartArea.Border.Color = RGB(255, 192, 0) 
            End With 
        End If 
        Sheets("Data").Select 
        If Cells(x, 11).Value = "G" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = x1Solid 
                .ChartArea.Border.Weight = 4 
                .ChartArea.Border.Color = RGB(0, 128, 0) 
            End With 
        End If 
         
    Next x 
     
End Sub 

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


I am trying to create a macro that will hide certain columns of an order entry spreadsheet if there is no data in the row four of a particular column. The order form consists of 20 columns – one column for each product – and details of the product order are entered in rows down the column. The product quantity is entered in row four. When the user opens the template file all 20 columns are visible. I’d like the user to be able to click a macro button on the sheet that will hide all the columns in which no quantity is entered in row four of the column (i.e that product was not ordered). The zero quantity columns are contiguous, because the user begins entering in column A, then B, etc. and works across the sheet. Currently the users are just selecting the zero quantity columns and manually hiding them, but I’d like to automate that process.

I am also trying to come up with a macro that will hide workbook sheets if cell A1 is blank, and unhide the sheet if cell A1 contains a value. (This is part of the same order process file. I apologize for asking two questions in the same post, and will be glad to expand on this quesiton if it is not clear.)

All suggestions are sincerely appreciated.

Hi,

I need to Color a particular column based on the value that is entered in the First column of that specific Row.

Eg: A1 contains 4, i need E1 to be colored
A2 contains 5, i need F2 to be colored.

Do i need to write a macro for this or can conditional formatting work ?

Hello,

I am trying to write a macro to highlight a group of cells based on the cell value in column A. The cell values in column A will look like:

1
1
1
2
2
2
3
3
3

What I need is to highlight all of the rows with a cell value of 1 in one color and highlight all of the rows with the next cell value as another color and finally to highlight all of the rows with the next cell value as the first color.

I have tried to write a Macro:

Dim startCell As Range, cell1 As Range, cell2 As Range
Dim rowNr&
Set startCell = ActiveCell
rowNr = startCell.Row: colNr = 45

    For rowNr = startCell.Row To 200
        If (Cells(rowNr, 1).Value) = (Cells(rowNr + 1, 1).Value) Then
            Set cell1 = Cells(rowNr, 1)
            
        ElseIf (Cells(rowNr, 1).Value) < (Cells(rowNr + 1, 1).Value) Then
            Set cell2 = Cells(rowNr - 1, 45)
            Range(cell1, cell2).Select
            With Selection.Interior
            .ColorIndex = 15
            .Pattern = xlSolid
            End With
            
        End If
    Next rowNr
    
End Sub
But can't seem to get it. Any help would be GREATLY appreciated!

Best Regards,
Excel Macro Noob

I would like create a macro to delete all the rows based on the value of "Complete" in a column (Product Status) - however, I don't want to delete the header row.

Can some one help me?

Veronica

I have a IF formula in a cells H4:M4 that says HIDE or UNHIDE based on
a condition in another cell. I want to hide the column based on the
HIDE/UNHIDE value of the formula. I want to be able to change which
columns are hidden based on the result of the IF formula, as it changes
depending on the condition in another cell. I tried to follow some of
the macros in the forum, but got lost as most refer to hiding rows.

Thanks, Bill

Morning all, hope you're all doing well. New member here

I'll dive straight to the point....

Recently started an Excel unit in an IT/Admin course and having previously sailed through the basic Excel formula and functions (stuff like :IF, SUM, Concatenate, conditional formatting, FV, Subtotals), I've got myself stuck trying to create a nifty tool using Macros in VBA.

I've produced an "Investment Table" using the FV function to allow the user to input things like interest rates, payments, number of payments, years of investment etc, and using these values the FV function throws out the resulting value (future value). Now, on a separate page I'm trying to "break down" this tool into numerous year-by-year tables showing how the value of the user's investment changes year-by-year and payment-by-payment.

For example, Mr Smith invests £100, 4 times per year, for 5 years, with an interest rate of 3.5%. Currently, all he can see is what his value will be worth at the end of the 5 year investment. But using the year-by-year tables he'd see a separate table for each year, and so he can see that in Year 2, Payment 3 his investment will be worth "X-value".

Here's where the macro comes in; in the main "Investment Table" the user inputs the "Years of Investment" (Cell D2). Using this (number) value, and on a separate sheet, a macro needs to produce/replicate the year-by-year table based on the value in cell D2. The aim of this is to save the user having to C&P the table or create their own, X-amount of times. The macro is needed because the tool must be flexible to account for different lengths of investment - and so obviously it's not practical to have to C&P if a user is investing for, say, 25 years.

Put simply, "Table X" must be copy & pasted Y amount of times, where Y = D2
I've tried to find somewhere to start in VBA, having done basic macros in it before, but I'm lost

Hope this makes sense but I will of course clarify if needed.

Thanks

I am new to VBA. My thanks to the community for the education. I have built a spreadsheet for work and am nearly done, but have hit a snag. Using information found on the site, I have written code to hide/unhide rows and make other entries based on cell values. There are 3 main parts to my code, all in the same Worksheet Change macro. Two parts work fine.

The remaining part works great, hiding and unhiding rows when I change the value in the target cell. Unfortunately, when I select another cell everything unhides - including the parts that were hidden based on the target cell entry

Because it is a bit long (the case goes to 16), I have only posted partial syntax (repeat case 1 & 2 thru 16). After the problem sample, I have posted a complete macro sample. I do not well understand the rules for indenting code lines and apologize for any confusion:

Problem Section:

	VB:
	
 
    Select Case (Target.Value) 
    Case "0" 
        Range("97:112").EntireRow.Hidden = True 
    Case "1" 
        Range("97:97").EntireRow.Hidden = False 
        Range("98:112").EntireRow.Hidden = True 
    Case "2" 
        Range("97:98").EntireRow.Hidden = False 
        Range("99:112").EntireRow.Hidden = True 
    Case "16" 
        Range("97:112").EntireRow.Hidden = False 
    End Select 
End If 

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

	VB:
	
 Range) 
     
    ActiveSheet.Unprotect Password:="ABC123" 
    Application.ScreenUpdating = False 
    Rows("88:93").EntireRow.Hidden = Range("C18").Value = "No" 
    Rows("95:117").EntireRow.Hidden = Range("C18").Value = "No" 
    Rows("31:36").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("70").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("118").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("140").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("157").EntireRow.Hidden = Range("J30").Value = "No" 
    If Target.Address = Range("J89").Address Then 
        Select Case (Target.Value) 
        Case "0" 
            Range("97:112").EntireRow.Hidden = True 
        Case "1" 
            Range("97:97").EntireRow.Hidden = False 
            Range("98:112").EntireRow.Hidden = True 
        Case "2" 
            Range("97:98").EntireRow.Hidden = False 
            Range("99:112").EntireRow.Hidden = True 
        Case "16" 
            Range("97:112").EntireRow.Hidden = False 
        End Select 
    End If 
    If Target.Address = Range("J30").Address Then 
        Select Case (Target.Value) 
        Case "No" 
            Range("H32:H34").ClearContents 
        End Select 
    End If 
    If Target.Address = Range("J38").Address Then 
        Select Case (Target.Value) 
        Case "No" 
            Range("J44").Value = 0 
        End Select 
    End If 
    ActiveSheet.Protect Password:="ABC123", UserInterfaceOnly:=True 
End Sub 

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


I have a problem that I hope someone can help me with.

I have 2 workbooks that I am working with.
I wish to copy data from workbook 1 to workbook 2 based on the following criteria.
A value in cell "B1" of workbook 1 needs to equal a value of a cell in column "A" of workbook 2... So it needs to find the corresponding cell with equal value of cell "B1" in column "A". Note the value in cell "B1" is the "key" for the records.Once the "key" has been found in row? in Workbook 2, i wish to paste the value in cell "D1" of workbook 1 into the cell in column "E" in this same row as the "key".Is it possible to do this and if so how can I do this?

Can someone please help me?

Thanks

Regi


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