Over 1 million tech questions and answers.

Change Range Cell Color Based on Another

Q: Change Range Cell Color Based on Another

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.

Preferred Solution: Change Range Cell Color Based on Another

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: 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 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

So first of all let me thank everyone that had input on my last post....i certianly appreciate the help.
so heres what i got going now. i have formatted the class rosters that will be used and i have a master data base of numbers that automatically updates each time a new class is added. and while some ppl think its great others are like well why cant it automatcally populate a list of everyone who failed, got dropped, or didnt show up. so thats what i need to do. i need some vba help on this one ( i actually got them to let me use macros now). i have the class rosters set up to where if a student does not show up then the row get highlited red and yellow if they get dropped, and i will need to add another color like blue for fail. anyways, the workbook is built like this: there is an annual training sheet ( total of all monthly numbers), then 12 monthly training sheets, and 52 weekly training sheets and then an unknown number of daily class rosters. looks something like this:

/ANNUAL//JAN 2011//3-7 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//10-14 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//17-21 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//24-28 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//FEB 2011/......ECT.

ok so what i need to do since the tabs that say "class 1, class 2" and so on will actually have their own unique name depending on the date, type of class and location it was taught. is to be able to have a vba macro (that i will use on each weekly tab and just change the fi... Read more

A:need to search multiple sheets and copy based on cell color

If it's a macro, you can test if .font.color = vbred then .... if .font.color = vbgreen ... etc.

If other then you would have to list the color value or colr index as a variable and test on that.

Read other 3 answers

I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30

A:Excel macro or formula - change field based on other cell

Read other 7 answers

Found it never mind thanx though

A:Solved: Change Cell Color

Hey Dreambringer, it's always best to leave your original question and have a seperate post of your solution. This is so that if somebody else comes to the board and searches for the same problem, they have problem & solution right there.

Read other 1 answers

Wow... it's been a long time since I've posted here. Hope everyone is well.

Question about Excel. Is there a way -- without having to program -- to change the color of the active cell? When I do a search, for example, it is really hard sometimes to locate where the cursor has landed (the cell that matches search criteria). If I could change the focus to another color, it would be nice....

Read other answers

In Excel, can I use an IF statement to change the background color of a cell?

A:Solved: Excel cell color change using an IF statement

Read other 16 answers


My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:


Sub Macro14()
' Macro14 Macro
ActiveSheet.ChartObjects("Chart 1").Activate
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?


A:Excel Macro to automatically change chart line style based on cell text


I'm not familiar with charts. But, see if this helps.


Read other 3 answers

Hi all,
I borrowed the following code from this board, and need it modified.
Option Explicit
Const MYRANGE As String = "A1:A10"
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Sh.Name <> "Sheet1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Sh.Range(MYRANGE)) Is Nothing Then Exit Sub
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Target.Value = ""
End If
End Sub
This code places an "X" in any cell in the set range by double clicking the cell. What I need it to do is change the font color of (black) text that's already in the cell to red by double clicking the cell. And, if possible, I'd like it to toggle between about 3 or 4 other font colors as well.
Any help would be greatly appreciated.
Thank you!

A:Need to change font color in a cell by using Before Double Click Event

Read other 12 answers

I am writing VBA code in Excel that adds Comment Objects to cells depending on the content of those cells.

When the comment is added a red triangle indicator appears in the upper right corner of the cell.

Is it possible/How can I change the color of those indicator triangles on a cell-by-cell basis?

I have a feeling that this is not possible but I thought I'd ask.

You can change the color of the error indicator triangles but you have to change all of them at once (can't change then cell-by-cell).

I can't find any reference to fill color of comment indicators.

A:Solved: How to change the color of the comment indicator in the corner of a Excel Cell

Built into Excel and cannot change.

However, as a workaround, you can draw triangle using AutoShapes over the comment indicators.

Hope this helps.

Read other 3 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 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'm trying (via a macro) to allocate a patten to a cell and also change the color excel uses on the pattern. By default it uses black.

I already have a macro that conditionally changes the background color of the cell. Problem is that when I print out on my cheap paper the colors bleed i.e. the black text bleeds into the yellow background. Therefore I want to use a pattern color to highlight the cells which uses less ink, therefore less color bleed!!

I have a quick workaround- print it draft all the time I want a nicer solution.

Thanks Phil

A:Excel- Macro-Allocate pattern to cell and change the pattern color

Phil, a word of advice. Don't try to program Excel to overcome an inefficiency of other hardware, namely your printer. You're going to be fighting a losing battle.

Easiest solutions?
1) Print in Draft mode
2) Get a new printer

I know it sounds daft, but those are your best bets.

Read other 3 answers

Hello all,
I am looking to modify the color of a Rainmeter skin that I have made, and want it to change based on the "average" color of the current wallpaper. I was thinking that the solution to this may be along the lines of how the Aero-glass used to work on Win7, or how the "automatic color picker" works now on Win8, where it changes the taskbar color based on the average color represented in your wallpaper.

Any suggestions as to where I might find code to get me started, or if any of you might be willing to help me do this? While this obviously isn't a task of utmost importance, I'm pretty sure we all love making things look the way we want them to.

Thanks in advance,


Read other answers

Am looking for a cell phone that has an external antenna that can be hooked into the electronics on my boat that has a range of approx. 35 miles any suggestions?


Read other 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

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?


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

is there a way to select many cells using the row and column number?
the range command permits to select knowing the letter and number of a cell, in 1 cell or many cells. range("a1:c5").select, or cells(5,12).select.

what I am trying to do is range(1,1:3,5).select. which I know it is wrong.


A:range vs cell selection in VB Excell

Is there any particular reason why you don't want to use the column letters in conjunction with the row number? Also, what is the purpose of selecting these multiple cells? Will you be copying the data to another worksheet? Please provide more details on what you are ultimately trying to accomplish.
We also need to know in your example above if you are only trying to select cells A1 and E3 or are if you trying to select the entire range of cells between A1 and E3 ?


Read other 1 answers

hello guys, I have a list of part numbers and a list of photos names as an entry i.e. 12345.jpg. Happens that the part number is the same as the photo name except with the .jpg extension.

I need to look in the part number column see if the same exists in the photo id column and if it does I need to write the photo id name next to the part number column.

If tried lookup, if's and last or(exact) formulas with no results.

Has anybody encounter a similar problem ?


A:Excel Cell range lookup

Read other 13 answers

Using Excell I have a table, on worksheet 'Attendees', with First names in column A and Last names in column B.

I have another worksheet that I use to print place names. Place names have to be printed 8 per sheet.

So I have a formula, on worksheet 'Placenames', in cell E7 '=Attendees!A2' and in F7 'Attendees!B2' then down for the next 7 rows. The actual place name cells use the formulae =E7&" "&F7 etc

I amend cell E7 to start at the next 8 names e.g E10 and drag/copy E7 over F7 then drag/copy E7 & F7 down over the following 7 rows.

I would like formulae in cells F7 and E8-F14 that would reference cell E7. I would then only have to amend Cell E7 and the place name fields would be updated.

Would I use INDIRECT, OFFSET or some other formulae in these cells?

Can anyone please advise?

A:Reference a range of cells from one cell.

Read other 6 answers


I am a begginer in excel programing and i don't know how to use cell value inside range command in VB

A:How to select a range in Excel using a cell value

Read other 6 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


I'm trying to enter a formula that returns a value when a loan balance is paid off. I've attached a spreadsheet that shows the costs and revenues of a project and the corresponding loan balance. It's very basic, but I'm trying to see if there is any formula that will return the Month that corresponds to the loan being paid off. I've been trying to use an "if" formula and when the loan balance is <0, but i don't know how to do it with just one cell. Let me know if anyone has ever done anything like this.


Read other answers

How can I hide rows based on the selection of a Data Validation in Excel.


If user chooses 1 from drop-down menu then hide rows 15-20 otherwise if 2 is chosen hide rows 25-35. I have a proc that will hide the rows but I don't know how to get the code to fire.


A:Hiding rows based on range content

Read other 7 answers

Hi there
I want to make a query that shows me the available rooms for a hypothetical hotel booking system.

I want to specify a date range for my booking (eg 15 Aug 2005 to 29 Aug 2005 - 14 days). I want the query to show me anything that falls outside that range and anything without a booking at all. However, I don't want to see any rooms that have any of that date range already booked (eg a room booked 13 Aug 2005 to 21 Aug 2005 should not show up).

I have the query mostly working, except for some reason it won't show up a room that has a booking prior to the date range I specified.

I have a field for the room number from a room information table, and one each for arrival and departure date from a booking table.

Can anyone point me in the right direction?


A:Exclude records based on date range (help!)

Hello, to help us can you post a zipped copy of the database on here, or at least post the Query criteia that you are using that doesn't quite work.

Read other 2 answers

The format is applied in Cell A2 and is based on a formula (C2+D2)>=0

I can get this to work fine for one cell, but I want to apply it down as far as cell A28 (for values in C28 and D28).

Is there an easy way to do this?


Read other answers

Hi there i wonder if you can help. I am trying to create a query by form which i have mostly working, apart from one control on the criteria form. In the database i have stored a candidates date of Birth, and on this form instead of using date or birth as a critieria i wanted to use a range in the form of a combo box, so the user can select one of the following.


I have created the QBF so that it creates a new query everytime. The code for this is displayed below. Any ideas how i can implement this range into the code. Any guidance or advice would be greatly appreciated. Thanks

Option Compare Database
Private Sub cmdSearchStudent_Click()
Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([Student ID])"
If Not IsNull(Me.txtStudentID) Then
swhere = swhere & " AND [Student ID] = " & Me.txtStudentID
End If
If Not IsNull(Me.txtNameEngSearch) Then
swhere = swhere & " AND [Student Name (English)] LIKE ""*" & Me![txtNameEngSearch] & "*"""
End If

If Not IsNull(Me.txtNameArabicSearch) Then
swhere = swhere & " AND [Student Name (Arabic)] LIKE ""*" & Me![txtNameArabicSearch] & "*"""
End If

If Not IsNull(Me.txtDOBSearch) Then
swhere = swhe... Read more

Read other answers

I have a simple worksheet which shows daily stats (1-31) in a monthly column.

A few weeks ago I set the column that any stat value within a certain range, the cell would be a set colour.

For the life of me I cannot remember how I did it.

ANy suggestions please??

A:Solved: Setting cell colour within a range

It's called conditional formatting.

Select the cells.
Hit Format-->Conditional Formatting...

Read other 3 answers

Hi all,

My first venture into this corner of TSG is to ask a question that'll probably make me look a moron but here goes!

Excel 2003. I have a cell who's validation is referencing a list with a source of '=EventSource'

If I then try a goto and select that named range (from the dropdown) nothing happens. Is this some kinda security that the original writer incorporated or just some vb trickery? Can I get to the source of that range somehow and add another item to the list?

Read other answers

Hi. Is it possible to have a macro select a random cell or range as needed by the user. Say the macro selects a cell or range, and then pauses to allow the user to select a random cell or range, whereas the macro then continues with whatever commands needed?

Bob from Tucson

Read other answers

I have several Excel 2010 workbooks that each have a cell of the same name, e.g., penicillin. I would like code that references that particular cell/range name no matter which workbook is open so that I can use the same code to perform a specific task. I'm thinking that the code would be something like:

Dim WorkbookName
WorkbookName = ThisWorkbook.Name

If Range("WorkbookName!penicillin") = True Then Range("DrugIndex") = 1

The code works if the actual name of the workbook is used (instead of WorkbookName) in the first half of the 'IF' statement so I think that my syntax is not correct when generically refering to the open workbook.

Again, your expertise is much appreciated.

A:Solved: Retrieve a cell/range name using VBA in Excel

Read other 7 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


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

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 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

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

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

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

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

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

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


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

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

 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