I would like a macro to run everytime A1's value changes.
The following works for an entire row, however, I would like range A:F highlighted not .entirerow.
I have thought of conditional formatting, but I thought the range I was using was to large. (A3:F40000)
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("a1"), Target) Is Nothing Then Exit Sub
If Range("a1").Value > 0 Then
Call mymacro
Else
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
End If
End Sub
I have attached a copy of my "budget". What i need is whenever you choose a option in A9 on PayCheck - DEC - 09 - B it will count the number of items on the right and hightlight them with a color. I use =COUNTIF('PayCheck - DEC-09-B'!E$2:E$1000,A9) in A11 to tell me the number of occurences but I would also like a visual effect with colors.
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001 q9002 q9003 q9004 q9005 q9006 etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
I need to create a dynamic range based on cell between a header and footer cell. The header cell ( A8 ) will remain static, however, the footer cell starts at A10 and then will move down as rows are added.
I have selected all 13 rows and 7 days and named them 'week1', and the next 7 days 'week2', the next 7 days 'week3' etc/What i want is to display the named range based on a single cell selection based on a cell value in a different sheet.
The basics of it should be "If cell C1 = 1 then show me 'week1' or if cell C1 = 2 then show me 'week2' etc.
Let's assume Cell A2, A3, A4, etc, only contain names/text.
Then let's assume I have set up cell B2, B3, B4, etc, to contain a percentage value based on a formula and some other entries I have made.
It should be simple, but I'm having trouble - how do I set up A2, A3, A4, etc, to highlight into different colours based on Column B's data/percentage falling into a certain range?
ie: - if B2 is between 0% - 25%, then highlight A2 green - if B2 is between 26% - 50%, then highlight A2 orange - if B2 is between 51% - 100%, then highlight A2 red
I am trying to create a table, based on what people predict in sports games. I have most of it in place, but I have hit a brick wall where one part of it is concerned. For example, cells M43:M56 have the scores for each week of what one person predicted., so it could be 2,5,7,2 and so on. What I then need the table to do is determine which round or week it is and display the relevant score. So based on the above let's say it's round 3, and his score would be 7 for the week, and the cell to the right of this shows the week's score plus his total score so far.
I'm a VBA beginner In my Excel worksheet, I have 1 column with data (column B). The data is in rows 5-250. This data is student majors.
Now, I want to assign each major a department and display the department in column U.
I was able to complete this for row 5 by using If-Then statements (see snapshot below):
[Code] ......
However, I would have to copy and paste that code over 200 times and replace the B5 and U5 with the correct row number. How can I get the macro to do this for each cell in a specified range?
I have a sheet that has six sets of three columns on it. The columns are labels Ball #1, Number Times Drawn, Last Date Came Up. The first five "sets" are the same except the ball # changes from 1 to 2 to 3 to 4 to 5. The last set has columns labeled Mega Ball, Number Times Drawn, Date Last Came Up. Yes I am doing stats analysis on lottery numbers for a project.
What I want to find is the last date drawn for each of the numbers, 1-56, and have it go into the third column of the 3 column sets.
I will use the first column set for example. I have numbers down the first column A numbered from 1 to 56 (In series). In the next column B, I have the number of times drawn for each number, which I have working. In the last column of the set I want to have the Last Date Drawn.
I tried the following function (Formula): =INDEX(Table1[Date],SUMPRODUCT(MAX((Item=A2)*ROW(Item)))-ROW(Table1[Date])+1)
This kind of worked. It returned a date but it was not correct. It actually said the same date for each number, which I know is wrong. I can't figure out where it is actually pulling that date from.
I should clarify that the drawing numbers are on a second sheet labeled "Mega_Millions_Numbers" in a table named Table1 and the Date column is A with a column label of Date. The table has column headings of Date, Ball#1, Ball#2, etc. through to Mega Ball column.
What I need is a function or formula to pull the last date drawn. I am NOT good in any way shape or form with VBA so if I can do this without VBA it would be best for me. I can follow along with formulas and functions well enough, just not VBA.
I will need to have this formula copied down 56 times per set and there are 5 sets so i will have to modify it for the other columns. (If your keeping track that totals 336 formulas).
I would like to search my worksheet and whenever it finds the word "Description" in column "B" then it should select the range "B:E" for that row and delete the cells directly above it.
This is what I tried but nothing happens:
lr = ActiveSheet.UsedRange.Rows.Count For Z = lr To 1 Step -1 If ("B:" & Z) = "Description" Then GoTo deleteit GoTo xt deleteit: z1 = Z - 1 Range("B:" & z1, "E:" & z1).Delete xt: Next End Sub
I have a spreadsheet broken out into about 30 4-row ranges, for example (A10:L13). All of the ranges use a using a vlookup argument:
=IFERROR(VLOOKUP($A10,Fund_Range,2,FALSE),"") in the second row.
Around 15 of the ranges will be populated based on varying data in the fund_range data table, the remainder will be blank. The first row of each range is a header row, I would like the macro to delete the range when there is no value in the second row (the blank ranges). Every attempt I have made starting with other code from the forum has come up empty. I think part of my problem is the vlookup formula existing in these otherwise "blank" rows.
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO. Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color.
if you have an error on a spreadsheet, why doesnt this come under " case else" when vba is run past it? I have this simple code, but it doesnt work when the value on the spreadsheet is an error ("N/A").
Sub RQVChng()
Dim lgrow As Integer
lgrow = 7
Do Until Cells(lgrow, 5) = Empty
Select Case Cells(lgrow, 17).Value
Case 1 Range(Cells(lgrow, 1), Cells(lgrow, 18)).Interior.ColorIndex = xlNone
Case 2 Range(Cells(lgrow, 1), Cells(lgrow, 18)).Interior.ColorIndex = 6
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
I am looking to set up a vba code for sending an email to a recipient based on whether the row AI19:AI30 shows the value 3,6,9 and i want it to send an email showing all of the 3 data all of the 6 data and all of the 9 data.
I have attached my code so far.
VB: Private Sub Workbook_Open() Dim w As Worksheet, c As Comment For Each w In ThisWorkbook.Worksheets If UCase(w.Name) <> "All Trades" Then Select Case w.Range("AI19:AI30").Value Case Is = 3, 6, 9
I have multiple sets of data that i need to reference based on the value in another cell. In this instance I am importing data to Excel from an outside source, this data is not consistent each time I import it and therefore based on the values that are imported I need to reference different cell ranges. Here is an example:
Import Data A B Alex 1 Ben 2 April 2 Harry 3
Data Sets Alex Data Range 1 2 3 Refine Help Token Cover Safe Coin Simple Aware Change
Ben Data Range 1 2 3 Sorry Away Candy Happy Home Soda Mad Neutral Water
My problem is that based on the name in column 1 of the import data I need to reference that data range so I obtain the correct column of Alex's data range with a random chance of any item in the column to be selected. Currently my formula looks like this: =INDEX(CELL("contents",A1),RANDBETWEEN(1,3),B1) and it is returning #VALUE, however when I put the Range Name "ALEX" in in place of the "CELL" formula it works properly the problem is I am not able to put the correct name in for each row.
I am trying to put together a calculation sheet for court costs and I need a cell to return a value based on the amount entered in another cell. For example, if I input a value between 1 and 5 in cell A1, I would like "Cat" to appear in cell A2. However, if I input a value between 6 and 10 in cell A1, I would like "Dog" to appear in cell A2. Is this something that can be done without VB?
For the real spreadsheet, I would need the following outputs in cell A2 based on the respective ranges in cell A1:
Output to A2 = $405 if input to A1 is < $50,000. Output to A2 = $905 if input to A1 is ≥ $50,000 and < $250,000 Output to A2 = $1,905 if input to A1 is ≥ $250,000
I've attached a workbook to explain my query further but essentially I have a data range, variable in size and I would like to split the range based on values in the range (delimiters if you like) into discreet ranges.
