I have a range C20:C38
In this range I have 15 codes that can be put in. If one of these codes is typed in incorrectly then a message box will appear stating "this code is incorrect please re-do"
can anyone help?
a few of the codes are
"S" "F" "O"
What i want is for a message box to appear automatically when the workbook is opened ONLY if Cell C4 (which is merged to F4)...
If there is information in this box then do not run macro
in the message box I want a box you can type in that will enter that information into cell C4. I know I will have to us a user form but what is the code I need?
Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.
I want the IF function to display a message depending on what the percentage in cell E35 shows;
IF, 0>> Display "Bad"
40>> Display "OK"
70>> "Good"
90>> Display "Very Good"
E35>=100 >>>> Display "Excellent"
I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?
View 2 Replies View RelatedI am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View RelatedI'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies View RelatedI have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies View RelatedWhat I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies View RelatedI have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.
In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.
code which highlights a cell depending on the criteria of another cell.
Example.
In B1 I have the formula =NOW() (we all now what this means).
In B2 I have a completion date.
I would like A2 to fill with a specified colour.
Can this be done so that it works down the whole sheet, if the date is in B5 then only A5 will highlight so basically only the A cell on the same row will change depending on the date in B on the corresponding row?
So, I just started checking Excel Macros. I'm working on a quite large data base where I need to search for a given part number and then move N columns to the right and display the text inside that cell on a message box.
Here's what I got 'til now:
Private Sub M0016216_Command_Button_Click()
Cells.Find(What:="M0016216", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
[Code] ........
how to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies View RelatedI'm trying to get the pink cell in THIS sheet to match up with the correct MONTH'S TOTAL in the pink highlighted column in THIS sheet. i couldnt figure out how to explain it any clearer.
View 6 Replies View RelatedI was wondering if it is possible to fill a row with a color depending on the value of a certain cell. Say, if the value of cell N5 is 0 then row 5 turns red.
View 3 Replies View Related[Code] .....
But what I want to do now is to strikethrough to eliminate unwanted data when a particular option is selected from the drop down list. In this case ( If User choose "Mens" for Gender, and NOT "Pants", Then Strike though the Result "NIKE". This works for the template in column E only using this code.
[Code] ...............
I tried to modify this like
[Code] ...............
So that every time x changes, the condition changes, but it seems like I cant do, Range("NIKE_" & x).
Attached File : Test1-2ed.xlsm
I would like to run a macro that searches column [AS] and hides any rows in that column that contain "0".
View 6 Replies View RelatedI have another macro request.
I would like to grey out a certain qty of cells depending on the 1st number in another cell. So 1st of all it would have to check in column B to see if there is any data and then check for the first number in that cell (there will be many numbers in the cells but we only need the 1st one (Ex.: B3 = 3 / 8 & 8).
Then it should start counting from column D the found number of cells multiplied by 2.
Ex.: if Cell B3 contains 3 / 8 & 8 it will count 3 x 2 = 6 (3 cells x 2 = 6 cells)
Then, if after that count (now at I3), in the next cell over (J3) if the is data in the cell above (J2) then it should apply an Interior.ColorIndex of 15 for the next cells until there is no data in the cells above (if nothing after K2 the grey will be applied to cells J3 & K3 only).
In three cells selected from a sheet, use the "MIN" to calculate the minimum value of the values ββof these three cells. After calculating the minimum value, apply a conditional formatting cell and it is colored green.
What I need is that, once established the minimum value in another column and cell specific, run a specific formula, taking into account: If the minimum is in a column X, and Y in a row, the formula should run only on the cell that is in the X -2 column, row Y-1 (two columns less and less row, where it is the cell whose value is the calculated minimum).
Example : if the minimum is in the cell " K24 ", the " Formula 1" must be executed in Cell "I23", if this minimum calculated were within the cell "H24", the "formula 2" must be run on the cell "F23" and if the minimum was in cell "E24"ββ, the "formula 3" must run "C23".
Once again I strugle with how I write the title for my IF Function problem, but anyway.
This is my problem:
I have a spreadsheet to count times
column D and F is:
D Start
F Finish, on column G, I have "F-D"
I start in D2 and type the first time.
F2 = D3 and
G2 = F2-D2,
If I have nothing in D3 I got an error, and if I don't fill all sheet I also got an error. What I want is, in the end of the row I have a cell with the final time "D36" where "F35=D36" if I made only half a way lets say until "D19" then "F19" should be = to "D36" and not = to "D20", stopping like this the calculations, doesn't give me errors.
I have a cell in a column A1 where is written: "XYZ 492" and in another Column is written "492".
Now I want to remove the "492" out of the first cell with a furmular.
I'm trying as below:
If F3 contains specific word i.e. FCL then only cell K3 and L3 are unlocked or available for data input.
Additional information:
F3:F10002 contain data validation list
I need to apply above condition on K3:K10002 and L3:L10002
I did some search on this forum and I came to know above can only achieve through VBA only. Is there is any possible way without VBA? I'm not good with VBA etc
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
I want to conditionally format cells K8, L8 and M8 so that when cell C3 contains a 3 it causes the previously mentioned three cells to fill black.
I cannot seem to work out how to do this using the options available under conditional formatting. Is there another way?
The purpose of the excel sheet is hour administration. In the first page you fill in which group it is about, what week it is about and the amount of hours to change.
There are several other sheets named after the group that are filled with some kind of agenda.
An example: I want to make a change to the amount of hours group X will be there in week 3. I fill out the 3 columns (Group, Week, Hours) and hit a button. It should now check those 3 columns for data and modify the hours for the correct group. It is not done yet but I'm running into a problem already, the function I use to find the row with the correct weeknumber in the Group sheet returns wrong numbers and I don't know why.
VB:
Private Sub CommandButton2_Click()
Dim Week As Integer
Dim WeekRow As Long
Dim Groep As String
Dim Uur As Integer
[Code] ....
What I am expecting to happen is this: It checks A1, B1 and C1, selects the correct sheet, executes the function FindRowByValue (the week number is in column B) and fills Week Row with the result, then returns to the original sheet, gives a debug message and repeats this 5 times.
The debug message shows that the Week Row is not returning the row with the week number I filled in but instead returns either the week number itself (so if I wanted to find week 5 it returns a 5, while week 5 is in row 28), or a different number that is incorrect.
I am creating many graphs. I have already split my data so I can use CurrentRegion.I want each graph to have a different title using the text value of a cell which would be (6 * j - 5, 2) inside the For/To, but I can't get it to work no matter how much I tinker.This is the code as is without any of my attempts inside to clutter it up.
VB:
Dim p As Long p = Sheets("ForWeb").UsedRange.Rows.Count For j = 1 To (p - 1) / 4 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Cells(6 * j - 5, 5).CurrentRegion ActiveChart.Parent.Cut Cells(6 * j - 5, 1).Select ActiveSheet.Paste Next
i have 5 lists of names which i want the sheet to enter depending on the cell value of C1. C1 is a drop down of 5 different departments so when C1=department 1 for instance i want A4:A25 to automatically update the list of names that i have against that department.
im sure its fairly simply but i just cant get any formula i use to work!
I want to add up items in column H, but only if Column J says "yes" in the same row.
I tried a few methods as I always try to do, but I came up short with this one.