I'm trying to make a macro. I'm experienced with Excel (took a college course on it) but am really new to VBA and making my own macros.
I need a macro that will take the item number in cell E4, and highlight the row (specifically columns B through R) where that unique item number is found (they're located in row B).
I'm trying to do a formula without using a marco. The formula would do the following:
- Highlight value in column A if the value is found in column B and on the same line value in column F and I are equal to zero;
- Value in column A could match many value in column B
I tried everything I know but can't seems to make it work.
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I have two different sheets. I need Cell E3 on sheet #2 to reference the value of sheet#1 Cell B6, but only if the 3 cells in front of sheet 1 B6 are true.
So, on sheet#2 E3 should only reference the value of sheet#1 B6 only if the words exist on sheet#1
B3 = First Aid, B4=Hospital and B5=date anytime in january
If the data entered in Column "I" starting from row4, exists in range "X4:X12" then "Pend for audit" should appear in Column "R" of the corresponding row.
below is the coding Iam using.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim StateName As String
Dim CName As String
Set Cells_Changed = Target(1, 1)
[Code]...
Excel Version: 2000
Workbook has the possiblities of 366 tabs at the bottom, but for a normal calendar year will only have 26.
The tab name is the ending date of the pay period without the year. So for Feb 25, 2007 the tab would be named 0225
On a yearly calculations page I am simply trying to say IF TAB 0225 exists then cell equals '0225'!A2 otherwise the cell should be blank.
I tried this, but gets a reference failure instead of putting nothing...
=IF('0102'!A2=0,"",'0102'!A2)
In this example the possible tab name would be 0102. If the tab exists, everything works fine, but since in this case it doesn't, I get a reference failure. #REF!
I NEED the result to be empty if the tab doesn't exist.
Any idea would be helpful. I'd rather not have 366 tabs when only 26 are needed for any calendar year.
I have a worksheet (SHEET1) where users would enter their ID Number and then they would hit a Submit button. The submit button would then add that ID Number into another worksheet (SHEET2) in a running list of ID Numbers. But before it is submitted, I would like to check for two things:
1. ID Number must be 9 characters long (numbers and/or letters)
2. ID Number must not already exist in SHEET2
If condition 1 is not met, then just have a message box asking user to check their entry and try again.
If condition 2 is not met, then just have a message box saying "thanks" but no addition will be made to SHEET2
I am trying to make a spreadsheet for bank reconiliation. I found this formula, =if( countif($B1:$B$1000,D1),D1)=0,D1,"") but there are two open parathese and three close. What needs to be corrected?
View 3 Replies View RelatedLet me see if I can make this succinct and clear. Cell AQ2 (where the formula below resides) looks at the value in cell E2. It then references table $K$3:$U:$100 on sheet "LookupTables" to get the value to display. It does this (and works great) with the formula below:
Code:
=IF(ISNA(VLOOKUP(E2,LookupTables!$K$3:$U$100,7,FALSE)),"",
IF(VLOOKUP(E2,LookupTables!$K$3:$U$100,7,FALSE)="","",
VLOOKUP(E2,LookupTables!$K$3:$U$100,7,FALSE)))
I would like to add one more argument. I would also like it to reference cell U3 in the LookupTables sheet and if there is anything in that cell to go ahead and complete the formula, but if there's no value in that cell, it should return no text and be blank
i have two columns in my worksheet A and B. Now col. A contains of words and column B contains of long strings. I want to make those parts of col. B which match to the content of A.
For Example It is now:
Col. A...................Col.B
word....................öladföljadfwordpapdfadlk
word2..................öadsfoewordpasdfword2rs
Should be:
Col. A...................Col.B
word....................öladföljadfwordpapdfadlk
word2..................öadsfoewordpasdfword2rs
I am attaching this example as excel file. I will be very thankful if some one could add the formula to this file and upload it.
I need a formula that will look at cell A1 and compare it against column B, if it finds it, show that value, if not, show "not found".
View 6 Replies View RelatedI have working data on list sheet and added data on compiled sheet.
I need to match duplicates and delete these rows from compiled sheet.
I've tried coping to the list sheet and use "Remove Duplicates", but it rids me of the data that has been processed and has fills applied.
I'm looking for a macor that can do this work for me without losing data on list sheet.
This data reaches into the thousands and I'm looking to run this off the CASE column.
Im trying to write a formula that will check if a name that I have in column A also appears in column B and if it does I want it to return a value for example TRUE in column C.
I'm currently doing a survey and I have all employees in column A (600persons) and then column B I enter thos that have taken the survey. So If an employees name exist in column B he/she has done the survey and then they I get the value "TRUE" or "1" or something else. So in this way I can easily see who has and who hasn't done the survey. I've been trying with VLOOK and so on but I can not get it right? Maybe I need VBA?
I've a spreadsheet and I need to find cells in Column A that contains certain text and subsequently being able to compare the data in 2 other cells (column F and Column J) and post the results of comparison in Column L. Please see the tables below:
In this table, if Range("A1") contains abc, then data in Range("f1") would be compared against Range("J1"). If F1 = J1, then Range("L1") would say "ok" otherwise "needs to check" as in Rows 2.
Please note that the amount of data varies daily.
ColumnA
Column F
ColumnJ
Column L
abc
fff
fff
ok
abc
fff
ggg
needs to check
bbb
fff
fff
I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
For Example:
Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie
[code].....
I am trying to set up a conditional formatting which will look at the cell contents and check if the value exists in a range.
The range to compare will be over multiple columns and multiple rows.
trying to find the value of column A (date field) if it exists on the row that the cursor is presently on. I'm trying to check for the value before performing the next function to make sure the user is on a valid row. Cursor could be on any column A to F. Columns are always 6 but the rows can vary.
View 7 Replies View RelatedI'm new to excel and have had a hang up with the MkDir feature. I would like to check if a folder exists based on a certain cell value, and then create the directory if it does not exist. This is what I have so far.
Dim newFile As String
Dim Path As String
newFile = Range("D5").Value & " " & "op" & Range("B200").Value & " " & Format$(Date, "yyyy-mm-dd")
Path = Range("A210").Value
[Code] ..........
I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example.
14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.
View 4 Replies View RelatedI have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
If I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use. I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.
View 4 Replies View RelatedLet'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
How can I change the font color or highlight cell A1, when I select cell A2. Cell A2 is still blank, no value entered yet. I also would like cell A1 to return to original font color or no hightlight when cell A2 is no longer selected (active), whether a value is entered or not in cell A2. Let me clearify, (A1 thru N1) and (A3 thru N3) have diferent dates and the font color is white, as is the cell too. Therefor you can not see the dates in (A1 thru N1) or (A3 thru N3).
However, when any cell (A2 thru N2) or (A4 thru N4) is selected (example A4), the cell above it (cell A3) changes font color or highlights, so the date can be seen in (A3). But when cell (A4) is no longer selected the above cell (A3) hides the date (changes back to white font on white cell). Data or a value is not necessarily entered into (A4) for (A3) to change. (A4) is only selected to show the date in (A3). But if data or value is entered into (A4), the date in (A3) will still be seen until (A4) is no longer selected/active.
i want to break content of my cells in excel into lines. Normally it is done with [ALT] + [ENTER]. How can i use find and replace(or other method) to break contents of all cells where "µ" exist. I MS word that is pretty easy but i dont know how to do that in excel!
View 4 Replies View Relatedi have got a spredsheet, with clients names on it and in cell Q i have the clients account number, when iam looking at the clients name i also want to see the number, is there any way to highlight the cell i am on.
What i mean isusing the arrows keys i want to make the row highlighted, is there a way to do this
I have a file in which I Concatenate the cell values like A1, B1, C1 and this vlookup of this concatenated value to the another range where same concatenate is used.
what I want to do is:
highlight the cell which is different in two concatenates.
For ex.
VLOOKUP
CONCATENATE
Value 1
Value 2
Value 3
CONCATENATE
Value 1
Value 2
[code].....
As you can see, in the above example I want to highlight the cell value which is different in two concatenates.
i have a sheet which contains large data.All the cells as formated as TEXT
e.g. value -863.45 appears as 0000-863.45
I have 3 types of values
1. headings => HEAD-1-TEMP-DATA
2. Only '-' sign => -
3. Numeric values with -ve sign => 0000-863.45
i want to hilight on Type3 values ..... is it possible with conditional formatting.
Or any other way w/o changing any value in the original sheet?
Have ABC row and EFGH row and just want to color the cells that only have
double numbers in it....In Conditional formatting----
example---All are in separte rows---
ABC ------EFGH
123-------5544-----55red----44red
332-------4498-----33red---44red
212-------2132-----22-----22
just the doubles I need to color them RED---
[Code].....
I am running a time series linear regression on a credit card company. The left most column is the amount of cards they had on a certain year and the prediction is the cards the regression formula predicted. The + and - are the columns added or subtracted the standard error which is 3.25. I am trying to highlight the closest +/- number to the original cards. For example, in the first Row 76.98 is closer to 78 than 83 is so i would like to have 76.98 highlighted...showing that subtracting the standard error gave us the closest prediction. The second column the 87.085 cell should be highlighted because adding the standard error gave us the closest prediction to 86.4.