Over 1 million tech questions and answers.

Solved: Excel Macro - Highlight Certain Cells

Q: Solved: Excel Macro - Highlight Certain Cells

I know this is probably very simple, and I am going to hit myself once the answer is found, but I cannot seem to think of the solution for the question below.

I need to highlight all the cells in a column that does not contain a zero. How would I go about doing this with code?

Preferred Solution: Solved: Excel Macro - Highlight Certain Cells

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel Macro - Highlight Certain Cells

Read other 9 answers

I have a macro that marks correct answers based on the correct value in B2 - B17 and AI2 - AI17 being the wrong answer.
Also if B2 - B17 is entered incorrectly that cell color turns red. If the answer is correct the cell color turns yellow and is added up at the bottom.

What my macro does not do is change the cell color to red if C2 - AI2 does not match B2 or AI17. Also if AI2 - AI17 is not entered correctly the cell color should change. Here is the macro. Also a file is attached with the macros. When the changes are made to the macro it should find mistakes in the attached file, some of the cells that are wrong are C2, D2, C3, D3, C15 there are others that are wrong.


Sub Update()
Dim Rng1 As Range
Dim Rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next

Set origRng = Range("B2:B17")
If origRng Is Nothing Then Exit Sub

For Each Rng1 In origRng
'Check contents of Rng1
If Rng1.Text = "" Then
'Rng1 is blank, skip this row
Range("B" & Rng1.Row & ":AI" & Rng1.Row).Interior.ColorIndex = 0
'Rng1 is not blank, run match code
Set compRng = Range("C" & Rng1.Row & ":AI" & Rng1.Row)
bMatch = False
For Each Rng2 In compRng
If InStr(UCase(Rng2), UCase(Rng1)) = 1 And Rng2 > "" Then
... Read more

A:Highlight cells if they don't match one of two cells (macro)

Any ideas?

Read other 3 answers

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)


A:Solved: Copy selected cells to chosen cells in Excel with Macro

Read other 14 answers

Hello! I am new to Macros and have absolutely zero experience in Visual Basic. I am trying to record a Macro that will loop through an Excel 2007 document and highlight all empty cells. I found this online....

Sub HighlightEmptyCells()
' Select cell A2, *first line of data*.
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
End Sub

But do not know what code will highlight the empty cells. Or, if this code will loop through the entire spreadsheet. Any suggestions?


A:Macro to Highlight Empty Cells

you could just use conditional formatting across the spreadsheet
In Cell A1
Format Cell
Conditional Format
change to formula is
The change the format ot a fill pattern or what ever you want for true
and then copy
paste special
and copy to all the active cells you want to test
for the macro this should work


ActiveCell.Interior.ColorIndex = 36

- theres 56 preset colours

Read other 1 answers

I want to Highlight Multiple Values with an Excel Macro. I want the macro to loop though and grab the list/range of values from another sheet and use it in the Array.

I want to avoid having to enter such a big list of values for the Array. I want to use something like "SearchVal() = Range("Lists!A2:A62").Value" but I get an "out of range" error. Here is my code now.

Sub Highlight()
Dim SearchVal() As Variant
SearchVal() = Array("ALM", "AXV", "AZZ", "BCT", "BHN", "CRE", "CSI", "CVM", "DCH", "DEK", "DER", "ESE", "GCV", "HCO", "HND", "HSI",
"IBO", "ILW", "KCM", "LEO", "LES", "LPA", "MCY", "MEX", "MHS", "MRM", "MSL", "NCL", "NTX", "NZI", "PHC", "PLA", "PPH", "PSE", "PUV",
"PVN", "PWL", "RKC", "RSN", "SCM", "SEL", "SGA", "SHC", "SRG", "SVL", "SYV", "TGC", "THA", "TSL", "VAV", "VID", "VTA", "WHE", "WHH",
"WIH", "WWJ&qu... Read more

A:Solved: Highlight Multiple Values with an Excel Macro

Read other 8 answers

This is probably a very simple macro but programming isn't my strong point.
I need an Excel Macro that will be able to scan the first column of a worksheet row by row for certain document numbers i.e. "09-005", "09-1052", "586463" and highlight them. There is a substantial list of these.
I managed to figure out that I need a range and how to highlight, but am getting stuck on how to find the multiple document number values.
Any and all help is greatly appreciated
Thanks in advance

A:Solved: Highlight Multiple Values Excel Macro?

Read other 8 answers


I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.


A:Excel macro to format input cells vs calculation cells

I wanted to point out that this can be done without using a macro. In Excel you can click EDIT >> GOTO >> SPECIAL and put a check in the option labeled "Formulas." After clicking OK all your formula cells should be automatically selected on the sheet. Once all cells have been selected you can change the font, background color, etc. for all the selected cells at once. If you prefer to use a macro instead you can try the code I provided below.

Sub FindFormulas()

For Each vcell In ActiveSheet.UsedRange

If vcell.HasFormula = False Then

vcell.Font.ColorIndex = 5


'Replace with your code to execute if cell contains formula

End If

Next vcell

End Sub


Read other 1 answers

Hi there,
I know this question gets asked in various forms time and time again but I'm too much of a novice to make those solutions work for me.
I have a list of data but each item is spread over two rows making it unsortable.

_ A B C D E F
1 a b _ _ _
2 _ c d e f g
3 a b _ _ _
4 _ c d e f g

b and c are both in colum B
whereas i would like to have

_ A B C D E F G
1 a b c d e f g
3 a b c d e f g

or even without the empty rows but even I can fix that.

If its not too much trouble I'd appreciate an explaination with any code so that I can try and change it for similar problems in the future.
I hope this is clear. I thank you very much for your time.

A:Solved: Macro to move excel cells

Read other 9 answers

Hello everyone,

I am wondering if anyone one would be able to help me. I have only just started getting into advanced excel use of macros and formulas for Excel 07. I have done ok so far except for the following macros formula needed tp change the background colour of a range of cells with more then the three options that conditional formatting allows.
I have a column of cells from C6:C80 which I have made all drop down selections from a list of four text items:
I was trying to get a macro that would colour the cells in C6:C80 automatically on selection of the drop down list based upon the four different selections available. And another macro to colour that whole row that has data in it based upon that same selection. The reason why two different ones being that I am unsure of what will work the best.

I know this is on the easy side of macros, but I am hoping someone would be able to throw me a bone none the less

Thanks for any help all.


A:Solved: Excel 07 macro to colour cells in a range

Ok turns out I didn't need macro and it can all be done with conditional formatting using the "Highlight Cell Rules". I was under the impression that you could only use three conditions but that was incorrect. Thanks for the help from Sweep at another forum.

Read other 1 answers

Hey all,

Here's my situation, any help is greatly appreciated:

I have a list of 13 items in one column. A couple of the cells will be hidden by the macro, but then what I need the macro to do is copy the first 10 unhidden items onto the clipboard.

What row(s) will be hidden will vary from week-to-week. If I were to simply have the macro copy a cell range, there would be a chance of it copying a hidden cell. I believe what I need is a way to have the macro do a count of how many unhidden cells it has selected, and stop when it reaches 10.

Forgive my ignorance but I am literally just picking up Visual Basic as of this week.

EDIT: Just to clarify, I have the hiding of the cells taken care of. I just need some help figuring how to copy the top 10 cells that aren't hidden.
EDIT 2: Found that I could delete the hidden cells. Worked perfectly.

Read other answers

Yes, can anyone help me figure out how to use the Find and Replace command (Ctrl-F) in excel to highlight the cells that's been found in yellow or any color.

Sometimes, I would have to verify and match serial numbers from a hardcopy and file that was sent to me. It would be great (and would make life easier) if I could have excel or the Find and Replace command to automatically highlight each cell that is found. Thanks

A:Excel: Highlight Find/Search Cells

Read other 11 answers

I have a large spreadsheet that has dynamic cells being fed by a pricing engine. I need to monitor when a price changes. How can i make the cell highlight everytime a price ticks so that i can immediately see the change.


A:highlight dynamic cells when they tick in excel

can you upload a dummy set of data
what do you mean by
price ticks

have a look at conditional formatting

Read other 2 answers


I have a following macro:
Sub BondPrices()

Selection.AutoFilter Field:=16, Criteria1:="BONDS"

For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
cell.Value = cell.Value / 100
End If
Next cell

Selection.NumberFormat = "0.00%"
End Sub
The problem is that every cell in the sheet gets divided by 100, not only the filtered cells (only BONDS).

Any help would be greatly appreciated.


A:Solved: Excel: Macro to divide specific cells by 100 with a filter

I've solved the problem myself by adding a line to the code that selects only visible cells:



Read other 1 answers

I'm writing an Excel macro to find cells based on format. The cells in questions are subtotals so the format is bold. I'm using the following code:

Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate

It works fine for most cells, eg it finds cells with values A1P-1MR Total, A1P-2RN Total, E1P-1MR Total

However it skips certain cells eg. E2C-2RN Total,
E3G-2RN, Total Grand Total
The format seems to be the same for all of these cells, so what's going on?

A:Solved: Excel macro selction.find skips cells

I just realized the problem. The number format was different.

Read other 1 answers

I'm trying to write an Excel macro that goes down a column. When it sees an empty cell, it should fill that cell in with the value of the cell that is just above it.

Thanks in advance.

A:Solved: Simple Excel Macro to Fill empty cells

Read other 8 answers

I have been given the task of updating our company price book, and am in desperate need of help with my macro.
Currently I have a macro that multiplies the numbers in the cell range by a percentage. However each page has prices in different cell ranges. I need help with an if statement to only select cells that have been formatted as currency (or at least cells that have a number in them), and to skip over blank cells, as well as cells with letters or letters and numbers in them.

Here's what I have currently

Sub Macro1()
' Macro1 Macro
[A1:H50] = Application.RoundUp([A1:H50*1.02], 2)

End Sub


A:Solved: Excel Macro needed to only select cells formatted as currency

Read other 16 answers

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!

A:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?


Read other 1 answers

Hey guys and gals,

Here's what I'm trying to do: I have an Excel spread sheet with 3 columns and many rows depending on the data. I need to be able to search the data in column C for about 10 or so words and highlight them. The problem is, the words in column C have been exported by a program so each word ends with a comma then the next word begins with no space so it's difficult to search. Example, Text1,Text2,Text3 and so on. Is there a macro or software that I can use that will search all the rows for column C and highlight the group of words I choose? I'm using Excel 2007.

Thanks for your help!

A:Excel Macro to highlight words?

Read other 7 answers

I have attached a sheet in which if I click the 'click here to schedule a surgery' button ....a userform will open....If I enter the details in the userform then it will be automatically saved under specific columns in the excel sheet....what I need is

If I choose 'surgery code' from the drop down then the scheduled cells should be highlighted with a colour........(If I schedule a surgery from 7 to 10 in OR 1 and if I choose the surgery code as '1' then cells from 7 to 10 under OR1 should be highlighted with yellow colour......similarly If I choose surgery code as '2' then it should be highlighted with green......similarly If I choose surgery code as '3' then it should be highlighted with blue...)
Thank you

A:Solved: how to highlight cells using a colour.....using VBA

The easiest way to achive that is to store the "Surgury Code Selection" on the Sheet where you currently "Merge the Cells" and use Conditional Formatting to change the Colour.

Read other 3 answers

Hi all,
This is a great board-- I have received a lot of help from here!
This is my next project.
Open the attached file and, on the right of the form, there are six cells with text.
I need to concatenate these with one space in between each one, then copy the group of them and use the clipboard to paste so I can open the "save as" dialog box and paste it as the filename. We need this in our environment to be able to create the file name quickly, paste it and save the file.
I want to be able to do this with a button that activates a macro.
You can see the button on the right side when you open the file. The macro assigned to it called "createfilename". The code is in module 1.
I got it started, but I know it needs help.

A:Solved: Excel macro to concatenate and copy cells for pasting to "save as" dialog box

Read other 7 answers

Excel spreadsheet 1 with 'X' number of lines in it.

Create a macro in sheet 2 that will copy a formula from line 2 (in this same spreadsheet) to line 'X'. I am simply doing a reformat in sheet 1 using some simple formulas so I can get this into a format to import.

First, determine number of rows in spreadsheet 1. I think this can be done with the counta function. I am trying to set a variable up that holds this value. This is what I have in the macro currently to do this:
Dim x

I am hoping that this set x = 10.

Second, I need to copy line 2 in spreadsheet 2 down 'x' rows.

I have tried relative positioning thinking that would work, but the macro still specifies specific cells. I thought I could use EndDown, EndUp to do what I needed, but it still puts in specific cell values.

The only thing that I need to know is how to use the variable in the VBA script. I don't know how to tell it to go to cell A(x). I have attached a spreadsheet with a rough example. The macro within this sheet is not completed.


A:Excel Macro that will copy formula 'X' cells

Read other 6 answers


Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.

A:Excel macro: Return values from 2 cells on same row

Read other 9 answers

This is just a curiosity post since I've been working a lot with excel lately. Is there anyway to create an IF formula to autofill cells based on a start date and then stop autofilling them on an end date? That probably seems really vague, so I'll attach an example.

In my spreadsheet, I have the costs per home on the right and a schedule on the left. Can I make an IF statement in the schedule that will read something like =if(B6=R7, Q7, ""), but then have the cost per month fill out the cells until Month 4?

I want to make a cash flow schedule that can be updated from as few entries as possible since I'm usually manually updating larger worksheets that become very convoluted.

Read other answers

Does anyone have a macro that will delete row that have duplicate cells in same row other columns?

A:Excel Macro to delete row with duplicate cells


Are you looking to delete rows that have duplicate data but the duplicate data is in different columns?
Can you give a small example?

If the duplicate values are in the same column then you can use the 'remove duplicates' tool.
Select the rows/columns then go to DATA and you should see the REMOVE DUPLICATES tool.


Read other 5 answers

Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.


A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.


A:Excel Macro to select specific cells

Read other 7 answers

I am having some issues trying to figure out how to select multiple cells using macros. My goal is to be able to highlight a certain name in a column of names, and this name can appear multiple times and not always in a set range.


(The blue font indicates a highlighted cell)

Is their a way for a marco to look at all of the names in column A until "Do Until IsEmpty()" has been completed and have the specified name in the macro "Larry" stay highlighted to view individual rows of information. Also, these names will not have blank cells inbetween them.

Thanks for all the help!!!

A:Excel Macro Selecting Multiple Cells

Read other 6 answers

two problems involving writing macros i would like to solve,

1st - want to write a macro to move to another worksheet, then edit a cell in that worksheet so that the value increases by one

2nd - would like write a macro - which again needs to move to another sheet, then copy a formula in one cell to another, to give two cells with same formula, and then fix one one of those cells (1st) so that it is no longer a formula - just a number

A:excel problem - macro to edit cells

You don't need to switch sheets in either case.

Sub Macro1()
Sheets("Sheet2").Range("A1") = Sheets("Sheet2").Range("A1") + 1
End Sub

Sub Macro2()
Sheets("Sheet2").Range("A1").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Range("A1").Value = Sheets("Sheet2").Range("A1").Value
End Sub

Read other 1 answers


This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have
Sub CreateInvoice()
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
' Keyboard Shortcut: Ctrl+i
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Windows("Blank Invoice.xls").Activate
Windows("Accounts September 2007.xls").Activate
Application.CutCopyMode = False
Windows("Blank Invoice.xls").Activate
Application.CutCopyMode = False
With Selection
.H... Read more

A:Excel copy & paste Macro for selected cells

Read other 16 answers

Hello, Can anyone help me. I need to add a macro that can iterate through a number of worksheets and delete all the data from cells that are unprotected.

I have tried a few things but being unable to find an diagram of the excel object model with properies and fields has slowed me down, if anyone can solve my query and point me to such a document that would be excellent.

A:Excel macro, change delete from unprotected cells.

You can use the cells "locked" property to determine if the cell is protected or not.

You can can use VBA to select the cells or you can manually select your range of cells and use a simple loop to take appropriate action. How many workbooks and/or sheets are we talking about running this code on? If you provide more details about your process we can give you more code showing how to loop through each sheet in the workbook or even how to loop through an entire directory of workbooks.


For each vCell in Selection.Cells
If vCell.Locked then
'Your code here
End If
If Not vCell.Locked then
'Your code here
End If


Read other 3 answers

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!

Read other answers

I have a macro that I recorded that does the following and I just need to change one thing for it to work the way I need it to do.

I select a list of cells in a different worksheet, and then run the macro.

This macro copies what I selected, goes to the worksheet named: Contact Fields Formulas. And then it pastes to Column D, starting in Row D4, the list of the cells that were copied.

Then, thru another macros I created before, when I am recording this macro, I run two other macros which one adds to the beginging of the words in the cells pasted, the " and the other macro I run while I am recording, adds to the end of the words in the cells pastd, the ".

Then the macro recorded goes to D4 and moves to E4, and makes E4 into E4=C4&D4, and it moves to E5 and then goes back up to E4, copies E4, moves to D4 and then down to the last cell with words that were pasted from D4 down, and then moves over to the cell to the right of the last cell in Column D that had the words as pasted, and then selects all cells going up to the E4, and pastes what was copied from E4.

Then, the E4 to the bottom of the last cell is still selected and it copies all of that, and then pastes it as a Paste Special with the concept Value, so that the Cells form E4 down, are the words of the two cells that were merged from the left of the E column cell.

So the question is, when I select another list of cells to copy and move over and do what the macro does, when in the area of

"moves... Read more

A:Excel Macro to Select Bottom Cell of LIst of Cells

Read other 8 answers

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.

Read other answers

Hi all,

I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.

I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.

The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.

I'm trying to write a macro that is able to automate the data transfer.

Eg. I need to:

copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.

Thanks in advance!

A:Help Needed for Excel Macro - Copy specific cells and paste into a Summary workbook

Hi all,

Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.

It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.

Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant

' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
On Error Resume Next

'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more

Read other 1 answers

Hello everyone,

I am new here and I can't find the right answer to my question.

I am making a sheet where people can enter their medicin costs.
Each medicin costs overview has 13 rows.

Now I would like to add the function, that when there aren't enough of these overviews, people can add these 13 rows in this page. So beneath a new overview should be inserted.

Now I tried several things, but every time the cells are pasted in the same cell (for example A42:H55)

What kind of code do I need so the cells are pasted underneath the last cells? Is this possible or not?

Thanks in advance

Ps. sorry if my English writing contains errors.

A:Solved: Copy cells via a macro

Read other 16 answers

Hi There

I'm not sure if this is possible or not. See attached workbook. This is only a sample to show you what I need. I have done a macro that adds two cells in Sheet2 Cell A1 that have sums in Sheet1. If you run the macro you will see what I mean.

The ranges in sheet1 always varies and the sums can be anywhere, so I have to run the macro every time. Is there a formula that I can use in Sheet2 Cell A1 that adds the two sums in Sheet1 no matter where they are without running a macro??


A:Solved: Macro that adds 2 cells

Read other 6 answers

Hi, I have a column in Excel that consists with time (minutes) from 0 to 80. I need a macro that will take each cell within a range (let say a column A1 to A6000) and do F2 and enter to each of the cells. I have too many cells to do it manualy..F2=>Enter. My lookup won’t recognize the numbers unless I do so. Can you help?


A:Excel Macro that does "F2 and Enter" for a range of cells

Read other 6 answers


I want to be able to highlight all my cells in the row that is complete,

I know how to do conditional formating, but this is for just one cell?

What I want is to have all entries along the row highlighted green when all the entires have been entered in, other than that it will remain with no highlight,

is this clear enough????

A:Automatically highlight cells

Read other 7 answers

Thanks in advance for any help you can offer in creating a macro that will accomplish the following:
Select an “open” Deal # from column A in Excel (2002) that meets the following criteria:
1. Has a Begin Date (shown in column F) earlier than today’s date.
2. Has an End Date (shown in column G) later than today’s date.

Each of approximately 6 sheets in the workbook contains about 400 rows of data, columns A-G (data in columns B, C, D and E is irrelevant to this process).

All Deal #s in column A which meet the criteria need to be collected and put in about row 405 with the following typed between each cell name: &" or "&.

The string of cell #s in row 405 ends up looking like this: =A3&" or "&A4&" or "&A5&" or "&A6&" or "&A7&" or "&A8&" or "&A9&" or "&A10&" or "&A11&" or "&A12&" or "&A13&" or "&A15&" or "&A17&" or "&A18&" or "&A19&" or "&A20&" or "&A21&" or "&A22&" or "&A24&" or "&A26&" or "&A27&" or "&A28&" or "&A30&" or "&A31&" or "&A33&" or "&A46&" or "&A47&" or "&A51&... Read more

A:Solved: need macro to select specific cells

Read other 16 answers

I am trying to write a VB command that will run a macro when any data is changed within in a particular range of cells upon exiting any of those the cell.

For example in my workbook if someone changes data in any cell in column I in rows 7 through 497 I want my Run2 macro to run.
Here's what I have.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = I7:I497 Then

Call Run2

End If

End Sub

I think its as simple as defining the range correctly but I can't find the proper command.

A:Solved: Run Macro When Any Data Within a Range of Cells Changes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I7:I497")) Is Nothing Then
End If
End Sub

Read other 2 answers


I am wanting to have a record data as default, and restore data to default buttons. So that I can set what the default inputs are for my model and also then when changes are made after saving the default to run a macro that restores the input values to the default.

Anyone know of a possible answer?

Edit: other than just dumping all the data in another tab and then copying it back.


A:Solved: Is it possible for a Macro to record the data in the cells?

Read other 6 answers

This is a reopening of another thread. I still have not found any answers that help. And we won't discuss the lack of support from MS.

I am running Windows XP, Excel 2007.

I have noticed the problem with extremely light shading in xp-excel. It makes things especially difficult when selecting multiple, remote cells or text using the 'ctrl' function, as you cannot see which section has been highlighted or not (note, many people do not understand the nature of the problem as when selecting one cell/group of cells there is a box around the selection, making it easier to visualise, however in selecting many cells, there are no boxes to assist).

I work with spreadsheets constantly and need to highlight for deleting purposes and this is giving me headaches, etc.

Thank you.

A:Solved: Excel Selection highlight color

Read other 16 answers

In range A2:A11 I have a range of numbers .
What I want is for a CF or code that would find the highest number in that range and hightlight it in say green and also the full row that contains this highest number.

Have tried this CF formula , based on a google search

Result is only cell in column A is highlighted (say A6) and not the whole of row 6.

Any suggestions welcome


A:Solved: Highlight whole row depending on 1 cell ,excel 2003

Read other 7 answers

What can I use (formula, etc) to check if a cell has a fill color or not? I have thousands of records with some with a yellow fill color. I need to move those that are filled to another sheet.

A:Solved: Excel: Formula to check cell color highlight?

Read other 8 answers

on a worksheet in cell E1 is a name copied from another worksheet in the workbook and in cell E22 is a calculated figure based on entries made in other cells, what i would like to happen is when the figure in cell E22 reaches a certain figure i.e. 50, cells k19-q19 change colour, i would prefer to use vba code, any idea's

A:Solved: excel cells

Try This....

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsk As Worksheet

Set wsk = Sheets("Sheet1") 'Change this to whatever your sheet is named

If Target.Address = "$E$22" Then

wsk.Range("K19:Q19").FormatConditions.Add Type:=xlExpression, _

wsk.Range("K19:Q19").FormatConditions(1).Interior.Color = 255 'Color is Red

End If

End Sub

Read other 3 answers

Hi All,

I want to have a button that copies cells B5, D5, F5, H5, J5, L5 from worksheet "Input" into a table in the next page starting in row 2 and adding the entries into the next row continually on worksheet "Trial", based on if there is a value in the B column. I'm pretty new to VBA and I've been playing around with some different suggestions but just can't get the hang of if it.

Can anyone help????

A:Solved: Copy cells VBA Excel

You are talking about a list or just row 5.
What is your excel version?
Pls upload a file with sample data.

Read other 1 answers


I have a sheet in excel 2003, that has a table that gets its data from different files, and does calculations on it. The size (= amount of rows) of this table varies, so I use the code described here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=512 to remove empty rows (the table is printed after the calculations are done, and having a half empty page before the rest of the text continues does not look nice, so leaving the rows empty is not realy an option). The problem is that the combination of the calculations and the VBA script to remove rows, is slowing my PC down considerably, since with each row that is removed, all calculations are repeated. Is there a way to force excell to only do the calculations the first time around? 1 round of calculations takes about 30 seconds, and it is usually repeated about 20 times (the amount of empty rows that is left after the calculations, which means that my computer is calculating for about 10 minutes, while this is completely unnessecary for my purposes.

Thanks in advance,

A:Solved: Let excel calculate cells only once

Application.Calculation = xlCalculationAutomatic 'it will change calculation to automatic
Application.Calculation = xlCalculationManual 'it will change calculation to manual
so you could add these to the VBA code to Turn to manual before you delete the rows and then turn back on to automatic at the end

Read other 3 answers