Over 1 million tech questions and answers.

validation based on another cell

Q: validation based on another cell

I have a spread sheet where I would like a message to pop up after I enter a value in cell G2 based on what the value of I2 is. like the following description:
In cell I2 I have "=(E2*F2*G2)/144" after I enter a value in cell G2 I want a message to come up if the value of I2/g2 is less than three and not equal to zero" Is this possible? If so could someone help me out with this?


Preferred Solution: validation based on another cell

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: validation based on another cell

This is the same as the challenge file but I added the code for this question

Read other 1 answers

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only

A:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?

Read other 3 answers


I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?


Read other answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers


I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers


I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i

End Sub


A:Solved: Clearing Cell Contents Based off other Cell

try this variation;

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub

Read other 2 answers


I have a couple of questions if you could possibly help me.

I have a cell where I want an employee to input the approximate (or actual) age of a person. To make this easier, I have created the following list - baby, child, 16-20, 20's 30's, 40+, Unknown. The problem is that sometimes the employee will know the exact age of the person - and I could do with capturing this information. Is there any way, other than elongating the list (adding on 0,1,2,3,4 etc), that the cell can be validated in such a way that you either input an actual age (say 00-100) or choose from the drop down list above?

The second question is -

I have 2 questions on an excel spreadsheet: -
1 - What is the number of clowns in the circus? Answer to be put in cell B3 (minimum 0, maximum 9)
2 - How many clowns had red noses? Answer to be placed in cell B4 (minimum 0, maximum 9).

Currently, both cells are validated to produce a drop down menu of 10 options - 0-9. What I want is to ensure the inability to place a higher number in cell B4 than B3. But I want to keep the drop down menu's (using list on validation). Can this be done?

Any help would be greatly appreciated.


A:Cell - Data Validation

It might help us to help you if you told us the exact program you are using.

Read other 3 answers

Trying to validate a cell as Decimal between 0 and 99999999999999.99. What I get is a rounded value like 12,345,678,901,234.1 instead of 12,345,678,901,234.12. Changing the edit to (13, 2) saves the decimal place but I loose the 10 trillion. Is this an Excel limitation or is there something else I can try. Using built in cell validation (Data --> Validation). What is the largest number Excel can handle? Thanks........

A:Excel Cell Validation

Excel's numbers are precise up to 15 digits, any other digits become 0's. This includes the decimal places.

Read other 1 answers

So far i have read you can't do this. So i'm wondering how you achieve this the correct way. I work for a gas field company and the database i am redesigning has a lot of fields that need validation, like well site names, company names etc. The person who built the original database had no validation at all, and you could find the word Production spelled 12 different way sometimes. Made it very hard to keep data readable.

So the part im working on now is the well sites list. I have a table called Well Sites. I have a main work orders table where they must choose a well site which is a lookup of the well sites table. This is fine and dandy, however. If they choose not to use a name in the drop down list, they can simply type anything they want, which is what i want to avoid. I want them to ONLY be able to type names that exist in the well sites table.

I was hoping it would be as easy as setting up a validation rule but from what google is telling me that is not even possible. So how do i control what user's enter. I do NOT want to type in a list of 600 well site names using OR for a validation rule. That is unmanagable, but i need to restrict what they can type so we don't get mis-spellings and mis-labeled wells.

Now i understand some things but some things i've just never done before prior to now. If i setup a one to many relationship between two tables then it seems to work like i want. Only values that exist in the other table can be entered into... Read more

A:Field Validation based on another tables data

Read other 6 answers

Hi there
sincerely hoping & wondering whether someone out here could help me by offering me your solution or suggestion ?

My situation is as follows:
In my worksheet there is about 600 cells scattered all over the sheet that are to represent different monetary value figures, that I have used NAME MANAGER to define as MONEDATA1, MONEDATA2, and MONEDATA3 (each holding around 200 cells, which is the approx.limit allowed by NAME MANAGER for Excel 2010). Thing is, my work revolves around dealing with 12 different countries of currencies, and so all these monetary cells, in CURRENCY format, thus need to be 1 out of 12 different currencies with respective currency signs, at any one time I do some calculation. So, they must convert into 1 of those 12 currencies, each time determined by a selection from a single dropdown list of 12 currencies located at the top of the worksheet in one particular cell I data validated, which is $C$6.

Now, I just need the worksheet to convert all the 3 groups MONEDATA1, MONEDATA2 and 3 simultaneously, instantly when 1 of the 12 currencies is chosen in $C$6, by using a VBA code. Is there a best way to resolve this need ?

Could you help me with the above problem ? Thanks in anticipation and advance ! Greatly appreciated any help.
cheers & have a nice day there,

A:Excel data validation and executing a code based on selected value


Read other 1 answers

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?



A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers

I have a fairly simple task. From Row 17 to row 51 and Row 96 to 163 , I want to delete the row if cells P and Q are empty.

From row 57 to row 94 I want to delete the row if cells B, C, P, and Q are empty

I'm not sure what I'm doing wrong here.

Sub CleanUp()

Dim endrow As Long
Dim x As Integer

Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row

For x = endrow To 17 Step -1
If Sheets("Work Order").Range("P" & x) = "" And Sheets("Work Order").Range("Q" & x) = "" Then Sheets("Work Order").Rows(x).EntireRow.delete
End If
Next x

Selection.delete shift:=x1Up
End Sub
Click to expand...

If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.

A:VBA Deleting row based on cell value

Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.

Dim endrow As Long
Dim x As Long

For x = endrow To 29 Step -1
If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then
End If

Next x
Why won't it remove the rows?

Read other 2 answers

Hello everyone.

I am trying to tweak a macro that was posted on this forum some time back, but I'm a bit of a N00B. The macro is from this thread that is now locked: https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/. What I am looking to do is generate two different emails whenever the value of a cell changes. All the data that needs to be part of the email is included in various cells. I've included sample data. If possible, could you provide code for generating the email and another for sending the email without opening Outlook (i.e., as soon as the value changes)? I've included sample data for your consideration. Thanks in advance for any help you can provide.

When Cell Value = In Progress
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to In Progress. The email addresses are in Column G.

Subject Line: "Web Request(s) Now In Progress"

Dear D2,

Your C2 B2 request has been received and is now in progress. Thank you!

My Signature

When Cell Value = Completed
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to Completed. The email addresses are in Column G.

Subject Line: "Web Request(s) Completed"

Dear D2,

Your C2 B2 request is complete and the changes are now live. Thank you!

My Signature

Read other answers

Does anyone know a way to manipulate the countif formula to count how many cells are "green" or "red" (with varying text in each cell), rather than the value of the cells?

Can this be done in VB?

A:Solved: countif based on cell format, not value?

Read other 10 answers

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated

Read other answers

Hi all,

I have a workbook containing 30 worksheets. The first sheet is named Master, while the other 29 worksheets are named with cities where we held branch offices (Los Angeles, New York, Chicago, Washington D.C., etc.)

I am trying to write a macro that will copy the contents of a row in the Master sheet to a particular worksheet based on the contents of the cell in that row that contain the location.

To illustrate:
Master sheet:
Columns are: Staff Name, Location, Project status

We have over 5000 staff.
I am trying to write a macro that will copy each staff's record to the worksheet corresponding to his/her location, depending on their project status.

So I am planning to use macro buttons labelled A, B or C (these are the values in the Project status column). So when I click on the button labelled A, the macro will go through all 5000 employees, and for those employees whole project status is "A", will copy that employee's record into a worksheet depending on his/her location.

For example:
Staff name, Location, Project Status
Joe Doe, Los Angeles, A
Jane Davis, New York, A
Mike Smith, Los Angeles, C

When I click on the macro for "A", it should copy Joe Doe's record into the worksheet labelled Los Angeles and copy Jane Davis' record into the worksheet labelled New York.

Thank you so much for your help. I've been searching all over the place for the command to use to reference the worksheet.

A:Excel - how to reference a worksheet based on value of a cell

For the love of PacMan, make sure you save your file BEFORE you try this. Try it on a copy of the file, even.


Sub A_Status()
'select the list of staff names on Master sheet, then run
For Each Cell In Selection
If Cell.Offset(0, 2).Value = "A" Then
DestinationSheet = Cell.Offset(0, 1)
Cell.Resize(1, 3).Copy
'assumes each location sheet has a header row
Range("A65536").End(xlUp).Offset(1, 0).Select
End If
Next Cell
Application.CutCopyMode = xlCopy
End Sub

Read other 2 answers

Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)


if C24 is 0, then hide column C
if D24 is 0, then hide column D
Sub Hide_Column ()

If Range("H24").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = False
End If

End Sub

A:Solved: Hide Columns based on value of cell

Read other 9 answers

 The laptop immediately shuts off when I pull out the power connector.  I've tried the troubleshooting steps listed on HPs website with no change. Thanks.

Read other answers

Is there a way to insert a new worksheet based on whether or not a cell contains data and name that worksheet with the contents of the cell.
Ex: A1= Data
worksheet would be added and named Data

A:Insert Worksheet based on cell content

Read other 9 answers

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.


Using Excel/Outlook 2010
ClickYes Pro is installed (don't know if it will be necessary or not)

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers

What is the best way to change cell colors based on a cell color (Not a number)?

I have a condition where a cell may have green background (conditional formatting), and on the next update, a different cell may have the green background (same condition, but not the same cell). I would like several cells in that column to change to the same color when this happens, they will be different rows, but will be on the same row as the change occurs ... it is dynamic.
Condition 1:

G8 background color is = green

Change adjoining cells to the right and down to the same color
(H8:K8 and G9:K12) The inclusive range would be (G8:K12)
Condition 2 on update:

G8 is back to normal but H8 is now green

Cells in column G are back to normal but adjoing cells to the right and down should now be green.
(I8:K8 and H9:K12) The inclusive range would be H8:K12)
Here is an example of the conditions above.

Condition 1
Row G H I J K
8 20 22 33 32 35
10 24% 15% 22% 21% 25%
11 80 82 90 88 89
12 44% 45% 48% 47% 50%

Condition 2
8 22 33 32 35 38
10 15% 22% 21% 25% 27%
11 82 90 88 89 90
12 45% 48% 47% 50% 52%

The cells I want to highlight will not be of the same value and there are formulae in every cell.

I haven't got the hang of copying the cells from Excel to the post. sorry about the formatting.

A:Change Range Cell Color Based on Another

In (nearly) simplest terms, and assuming from what you say that there'll NEVER be a time when G8 AND H8 are BOTH green, then

If Range("G8").Interior.ColorIndex = 10 Then
Range("G8:K12").Interior.ColorIndex = 10
ElseIf Range("H8").Interior.ColorIndex = 10 Then
Range("H8:K12").Interior.ColorIndex = 10
End If

You'll have to get back to us on what sheet event might fire this. & let us know if the actual cond formatting (a) blocks the code and/or (b) remains intact.


Read other 3 answers

i have 2 workbooks entitled [price] and [fruit] respectively

[price], sheet1!, cell A1 is a variable which can contains a cell address, eg $D$1

[fruit], sheet1!, contains information in cells as follows:
D1 = apples
D2 = bananas
D3 = cherries

in the [price] workbook, i want to have a formula in say, cell B1, which would use the cell reference in A1 to find the value in the [fruit] workbook.

so, if [price]sheet1!A1 = $D$1

then [price]sheet1!B1 = apples

note that the result "apples" was pulled from another workbook, [fruit]sheet1!$D$1.

if [price]sheet1!A1 = $D$2,
then B2 = bananas

and so on...
in the [price] workbook,

As A1 is a variable, i do not want to physically retype the value within A1, but rather draw reference to it inside of a formula. i hope this is clear as mud.

Is this possible?


A:Q: draw value from a 2nd workbook, based on a variable cell ref in 1st wb

In Sheet1!B1 (of Price), use


, where A1 contains the cell reference -- $D$1, $D$2, $D$whatever.

As explained yesterday, this (the INDIRECT function) will only work when both files are open ; to get it to work when the source file if closed, you need to download Laurent's add-in. Please do not start threads over.


Read other 1 answers

You must be bored with this question cos I've seen lots of them, however I can't seem to fathom out what to do..........

I've got a block of data within a spreadsheet 4 rows always in the same place.

How do I put into code:-

If column d43:e46 is blank delete rows 43 - 46 if not ignore it.

As I said I've tried a few things and it will delete the four rows regardless of them being empty of not.

Can someone help please.

Many thanks,


A:Deleting rows based on empty cell.

Is there anybody there? - Help!

Read other 2 answers

I have a worksheet that contains a budget, some of the line items are identified as allowances in column C, by the "allow" designation. The description of the allowance is in column B.

I want to create an automatic table on a worksheet called "Allowances" which searches column C for "allow", and if found returns the description from column B in cell A and the amount of the allowance from column F, in cell B.

Any suggestions?


Read other answers

Sounds like a simple thing but I’m having troubles finding an easy answer. Still searching ….

I want to create a catalogue for a customer of mine (basic – but might be more extensive later)

– showing a thumbnail picture and then a short product description. Then he can go into the doc and correct the descriptions etc.

Not sure if excel or word or other is the best place.

He has over 200 product pictures stored on disks and folders on hard drive. I have corresponding description in excel based on a code that is the photo name (+.jpg).

So, I have the product files name as HB1000.jpg and all I want to do is show the picture (say at size 100x100)

Ideally I’d like to build a reference based on the code and not have to insert each file.


So far – tried creating a formula/reference in excel with “Code” HB1000 (Cell A2) and image cell contains “=c:\productimage\”$A2$”.jpg” - can not get the pic to display

A:How to display images in Excel based on cell ref

The answer is already on here, I posted it fairly recently, it requires VBA to do it well.
see this thread

Read other 2 answers


I am using Excel 2010 and trying to resolve an if statement... in the file attached I require the following:

I need 1 of 3 results for cell G2 based on the first 3 characters of cell B2

M15### would equal PAB
M22### would equal NSY
Non# would equal None


A:Multiple IF Statement based on first 3 characters of cell

This was my attempt but not working:


Read other 2 answers

I am trying to hide rows in excel 2010 based on cell values in a certain column. I know that I have to use the VBA but I don't know how to do that kind of stuff. I know it should be simple to do but I don't know where to start. Let me know what you need in order to help me accomplish this task.

A:How to hide rows in excell based on cell value

Read other 8 answers

Dear Fellows

I am looking for help for a macro which can copy data to other sheet based on adjacent cell value which i define.

For example i have data attached in a sheet.

i want to copy data based on value present in E column to different sheets. if it is ES then all ES rows should be copied to sheet ES with roll number name father per and dept.

Same is for other departments, on different sheets. There will be 9 departments overall.

Looking for response

Read other answers


New here. I dug up a thread that Zack Barresse solved many years ago. I am looking to do the exact same thing. The link to the thread is below. My file is infinitely more complicated than what that user was asking for so I need a bit more help tuning the VBA. Link: http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html

Some specifics:

- I am using Outlook not Express
- Excel 2007
- All the functionality is complete for monitoring several live streams of securities data with several trade indicators.
- It is consolidated onto one sheet for manual monitoring (Picture below). Basically takes copious amounts of data and reduces it to just IF and AND functionality for the triggers for easy use from all the other sheets.
- The workbook will be open and running/refreshing on its own 24/7 as it is now.

I am a busy guy, I just need the VBA to automatically email me remotely when any of the 7 currency pairs causes a trigger when I am on the go. I can log trades from an app on my phone.

One other hurdle would be that if say (Using percentages to keep it simple) that a trigger would be if something reached as high as 80% to send the notification email. But where the system refreshes every 60 seconds it shouldn't send another notification each time it remains at or above 80%. Just the once. It may remain there for hours and that is a lot of emails.

Thoughts? and many many thanks in advance.

A:Excel - Auto Email based on cell value

Read other 10 answers

I have an Excel workbook with 400k rows Each set of rows contains a table name with the fields in the table. The number of fields is not the same for each table. For example:




I need to convert the field names so that they are in a row like this:


Is there any way for me to do this programatically? I'm using the transpose feature but it is time consuming and I have 400k rows. So basically, I want something like if the field in column A row 1 matches row 2, move that field to the right of row 1 and so on until the table name changes.

Or maybe i should dump the spreadsheet in access and try there...



A:Move data from column to row based on value of cell?

Read other 6 answers

Hi All,

I need to make a macro that will add borders to certain cells.
I have a generated report from another product so I don't know how many rows there will be until the report is generated. The number of rows is specified inside the report, so I do have access to it. It is stored in cell C5.

So what I want to do is add borders around C5 number of rows starting at A7..E7

I tried using a rule, to just put borders around cells that contain data, but that won't work because sometimes cells are blank and I still want the border around them.

Help?! Please

Thanks a bunch!!!

A:Excel Macro to add borders based on cell contents?

Hi Lori

Here is a macro that will take the value in C5 e.g. 10 and then apply borders to all cells in A7 to E16
Sub add_borders()
lRow = Range("C5") + 6
Range("A7:E" & lRow).Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlC9ontinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub

Read other 1 answers

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

A:Attachment based on cell value in a excel email macro

anybody ???

Read other 2 answers

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).

Read other answers

Hi guys,
I need to write a macro that is connected to many buttons, one for each row in spreadsheet, ask for a value to subtract, error check and (if ok) subtract from a corresponding value in column P.

When the button on a left hand side is clicked:
a dialog button should pop-up prompting a user to input the number of pieces to subtract from the corresponding cell (row based on the row of the button, column P)
error check the input number (show error if the value is smaller than 0 or larger than the corresponding value) ... I can write the error check by myself, so no need to do this step
subtract the number from the corresponding cell (i.e. user clicked a button located in cell A9, input 9,24 pieces in a dialog box and there are 15,97 pieces in P9 => the pieces in P9 should change to 6,73)
Thank you for helping me out!
Hope, the description is not very confusing.


A:Excel VBA: Subtract from a corresponding cell based on button placement

Read other 9 answers

I know that this is probably a pretty trivial problem, but I can't seem to come up with the answer. I am trying to set the selection focus to a cell in a column, based on an offset variable. The following is a little macro that I created to experiment with. As written, it positions the selection focus on cell C6. But I can't find the way to set it to C6 + Offset. The only reason for the MsgBox statement is to prove that I passed the argument as expected.

Sub MvTo(ByVal Offset As Long)

MsgBox "Offset " & Offset

End Sub

I thought I could use RnCn cell references and write something like Range("R6[+Offset]C3").Select, but I get an error return, so I'm missing something somewhere. Run-time error '1004', Method 'Range' of object "_Global' failed.

I am using Excel 2000.

A:Solved: Set cell selection based on a variable offset?

Read other 6 answers

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham

A:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.

[SIZE="3"]Sub Search()
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.

Read other 1 answers

Hey all,

I'm new and have no experience with Excel vba coding. Hopefully someone here can help me out.

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.

Thanks in advance,

Using Excel/Outlook 2010

A:Emailing multiple recipients from Excel Based off Cell Value

Read other 9 answers


I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?

A:Excel 2003 replace macro based on value in other cell

Read other 6 answers

I need to provide seqential row numbers from 1 to N that relate to cells in another column when the cell value is greater than zero. For instance, if the column with data is column d and I have data greater than zero in cells d3, d4, d5, and d7 I'd like the row count in column c to read number 1 for c3, 2 for c4, 3 for c5, and 4 four c7. Cells c1 through c3 as well as c6 should remain blank. What formula do I use?

A:Solved: sequential row numbering based on value in another column/cell

Read other 7 answers

I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

1. =AND(($G9<Today()),($I9=""))
2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
3. =AND((J9>G9),($I9="Active"))

If any of these conditions are met I would like an email to be generated.
(SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2

A:Excel Generate emails based on conditions of cell.

Read other 10 answers

Hi Guys,

Apologies for repeating an old question, but I am trying to build my spreadsheet so that it auto-hides any rows were there is zero premium outstanding for a client.

I had gone through previous answers, and I had tried to lift and amend some VBA code to achieve this. However, somewhere within this code or the way I've implemented it, there is an error, because it's not showing of hiding any rows.

Can you please take a look at the attached and fix it (in order to hide all rows with a zero in column C), and maybe point out what I was doing wrong or omitting. I have anonymised my data.

Many Thanks

A:Show/Hide columns based on cell contents

Apologies... the criteria column is actually F, not C, the "outstanding premium" column. The intention being to display only those rows where client still owe premium

Read other 1 answers

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!

A:Automatic Email from Excel based on Date in Cell

Read other 16 answers

Hello to evrebody,
I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.
On theattached sheet i explain what im looking for.
Pleaseo play it and hope to help me and give a solution please.
Thanks in advance.

Read other answers

Hey all

New here and new to VBA so I apologize if this is the incorrect place to ask this question or if I'm not including enough information.

Basically I have two forms, one form that is a cost est. form and another form is a proposal form (client sees this one). I have the proposal form pulling information over from the cost est. form. What I would like to do is on the proposal form if rows c13:c277 are blank (no data pulled from the cost est. sheet) they will hide. But when I add data (a number) into the cost est. form the proposal form pulls that and unhides that row.

I've tried all kinds of VBA code and none seem to do what I want. I can get the rows to hide but then not unhide. I don't have code to show because I have deleted it when it didn't work. Any help would be greatly appreciated.

Read other answers

In order for my wife to get reimbursed, my wife has to turn in a Mileage Log every 2 weeks. In her Mileage Log form, each cell in the "Starting Location" (Column B) and each cell in the "Destination" (Column C) has a list of addresses from which she can select. The lists are generated from the DATA Sheet mileage chart

As an example and in other words, if we select the address at 417 Victoria Dr., Kerrville in Column B and select the address at 110 Ash, Ingram in Column C. By looking at the mileage chart on the DATA Sheet, we see the distance between those 2 places is 10.6 miles. Presently, we have to enter that mileage manually in Column F.

Is there a way to have excel get the mileage number from the DATA sheet based on the locations selected on the Mileage Log sheet. I could probably use a lot of nested IF statements but that would be real cumbersome and very hard to modify as she has clients come and go from time to time in which case I would have modify each IF statement. I'm sure there is a better and simpler way.

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 8.1, 64 bit
Processor: Intel(R) Core(TM) i7-4790S CPU @ 3.20GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 12227 Mb
Graphics Card: NVIDIA GeForce GTX 750, 1024 Mb
Hard Drives: C: 1848 GB (1769 GB Free); D: 2794 GB (2536 GB Free);
Antivirus: Webroot SecureAnywhere, Enabled and... Read more

Read other answers