Macro That Locks Down Cells Based On A What Is Shown In Another Cell
I have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:
If F4 = CST
Then Lock down H4-S4 and AM4-AZ4
But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked
Is a password necessary for this? It is ou
I just want it to lock and unlock, not protect and unprotect.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
IF Function (few Words Shown Based On Figures In Specific Cells)
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best: A1 = 5 A2 = 0 A3 = 7 The function i am using looks something like this:- =IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?") However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well. Maybe it should look something like this but it does not work the way i am doing it. =IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell. Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items. I included an example of what i mean in this excel file which should clear up any confusion.
View Replies!
View Related
Macro Code Locks When Paste Is Used
Everything works in the script. It is a little slow on some of the users pc's.But they discovered a problem when they paste more than a couple values ,the workbook locks up. 1 I like to know how to speed this up? 2 I would like to understand as to why excel locks up. You can recreate the lock up by picking a name from the drop down list in column A5 then Copy that name and paste in A6 To A10. I'm using Excel 2002.
View Replies!
View Related
Workbook Containing Macro To Reduce File Size Locks Up When Shared
I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url] Option Explicit Sub ExcelDiet() Dim j As Long Dim k As Long Dim LastRow As Long Dim LastCol As Long Dim ColFormula As Range Dim RowFormula As Range Dim ColValue As Range Dim RowValue As Range Dim Shp As Shape Dim ws As Worksheet Application. ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?
View Replies!
View Related
If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it? 2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View Replies!
View Related
Macro Button That Is Always Shown
How do I programmatically freeze ( lock?) an autoshape so that it stays put even when the user scrolls up/or down? FYI: I created an autoshape and assigned it a macro, thus it becomes a software button, I want the button to stay put.
View Replies!
View Related
Printing Locks Up
I have a workbook that contains several macros and many formulas. It works beautifully until I try to print or do a print preview. Then it locks up excel and I have to restart. Any ideas about what is causing this?
View Replies!
View Related
Autofilter Locks Up Sheet!
Autofilter is turned on in a weekly spreadsheet that I receive. Functionality to move around by arrows or page up/down works until I select something out of an autofilter drop down. Screen locks in place. Can't arrow down or up (but can go left/right). Page up/down is broke also. If I exit and re-enter it works until I filter. Scroll lock does nothing.
View Replies!
View Related
Formula That Locks In The Number
In a cell there would be a formula, an "if" formula and I want to know is there something in Excel that will lock in a true statement. Like lets say in cell "A1" a formula would read =if(b1>1,.001,.002), I want to lock in a number when the statement is true. Cannot be change no more after when it is true.
View Replies!
View Related
Macro To Color Cells Based On Criteria
Column DColumn EColumn FTarget %DifferenceCash4%5.12%Large-Cap8.5%1.73%Commodities4%-1% I need to color code Col F based on the following criteria: If F2/E2>20%, then shade pink else no color. I only care about the absolute values. The 20% number can change depending on the category. So for commodities, the limitation is 30%. Like that I have a list of about 20 asset classes, 14 of which have the 20% constraint, rest are either 25% or 30%. I would like the cell color to be updated each time I import a file. Also I have other conditional formatting on Col F (like red color if negative number) so I don't know if another conditional format will work on the same cells?
View Replies!
View Related
Looping Macro Based On Range On Cells On Worksheet
Sub New_Book() Sheets("2006-07").Activate Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Sheets("2006-07").Activate Range("A1").Select Sheets("2006-07").Select Range("b3").Select ActiveCell.FormulaR1C1 = "=R[-2]" Dim ThisFile As String Const MyDir As String = "C:" With ThisWorkbook ThisFile = .Worksheets("2006-07").Range("b3").Value . SaveAs Filename:=MyDir & ThisFile End With ActiveWorkbook.Save End Sub The above VBA works by saving a copy of existing Workbook by refering to cell A1 and then Pasting Special worksheet 2006-07 which has external links. Is it possible to create a looping macro that refers worksheet("BUs") which list all business units I need to run and save? Range(A1) needs have a looping macro that refers worksheet BUs and then automatically saves files without manually changing cell references.
View Replies!
View Related
Check Multiple Cells, Fire Macro Based On Result
I have three cells where a user will input data, in some cases (2T Weld Condition) they will only enter in B12 and C12, but in the case of a 3T weld they will also enter data in the D12 cell. I then use a formula to check for the thinnest material and that is entered into another cell with a formula, B14. I then need to check the value in B14 to verify if it is above zero, but below 0.65 (mm). If it is then I would like to have a message appear on the screen notifying the user that they are outside the acceptable range. I cannot figure out how to use the information in cell B14 because it is a formula and my code only works with a direct value. The code I am using works if I point to one of the three input cells, B12, C12 or D12. How do I use the information in B14 to work with the code below. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$12" Then If Target.Value < 0.65 Then Run "MyMacro" End If End If End Sub MyMacro loads a userform with buttons, etc.
View Replies!
View Related
Macro: Changing Cell Color Based On Date In Cell
I did a search on this site and found some code I was looking for (see link: http://www.excelforum.com/excel-prog...e-in-cell.html - Leith Ross's response code). The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed. I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()". I assume I need to modify the code but am unsure as to how.
View Replies!
View Related
Macro To Comment On Cells Based On Formula's (duplicate, Length, No Entry)
making a macro that will scan for duplicates, length & empty cells. I have the sample file below that does conditional formatting but it doesnt help as much because I want to show the reason for the highlight's on a comment instead. sample file : [url] file that might help out: [url] checks would be: column A - duplicates and/or length should not be over 100 characters column B - duplicates column F - should only contain 2 comma's(or 3 keywords) all columns - check if no entries are found(empty cells)
View Replies!
View Related
Value Shown As #####
I have a value showing up as #####. When I auotfill the cell to the next on the right, it shows the right value. How do I correct this so it just shows the right value in the first cell?
View Replies!
View Related
Getting A Cell To Say One Thing Based On Other Cells
I have a worksheet that does a running total. It also correlates with another worksheet. But, for this one B4 through B23 is added together on B25, then on B27, B25 is subtracted from B3(original balance). In the cells B4-B23 there is a formula to pull information from another worksheet. I am wanting to incorporate a formula with that for if the total that is in B4 through B23 adds up to the original balance (B3 or B25) then it will say PAID. I have ...
View Replies!
View Related
Conditonal Formatting A Cell Based On Other Cells
I'm doing a Comparison of Price quotes from some different vendors and I want to highlight which one is the lowest. 4 Rows with Totals The Cells are B21, B43, B65, B87 I need each value in each location to be compared to the other 3. The other thing is that I also C through V that need the same thing.
View Replies!
View Related
Coloring Cell Based Off Of Other Cells Date
I am trying to make a certain cell range turn red on the week that a cell has listed. What this is being used for is to track the work of our contractors, and I currently have this set up to auto calculate the jobs that are being done and when, but I would also like to include something in there that when the contractor is set to head back to India, that day forward is listed as "red" under his name. This will allow me to see if I need to extend his work based on the number of hours he has left in his jobs. I will attach the picture of it...on the top you will see dates, and the pivot table on the bottom shows when he will need new work, but I am going to add a field that has the date he is set to leave, this is what I want the coloring of fields to go off of! ...
View Replies!
View Related
Fill In Cells Based On Other Cell Values
I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file. In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.
View Replies!
View Related
Copy Cells Based On Criteria In Other Cell
I want to copy cells A&B where there is a value in G to a new workbook. I need to loop through the workbook as there are 25 sheets in the workbook, and there 8 workbooks to go through. There are 31 rows on each sheet, and there are only a few values in column G on each sheet. A is customer name, B is Account No, G is Insurance premium paid, columns C to F, and H to M have other accounting data in them. The idea is to create a global list of all customers who have already paid their insurance premiums.
View Replies!
View Related
Combining Cells Based On Value Of Adjacent Cell.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks. Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names. A B 1 Car, 1 Taxi, 2 Bus, 2 Lorry, 3 Skateboard, 3 Bike, 1 Motorcycle, What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View Replies!
View Related
Counting Unique Cells Based On Another Cell
I have 2 columns that I need to use simultaneously to conclude something. Column A Row 1: Dog Row 2: Cat Row 3: Bird Row 4: Dog Row 5: Dog Row 6: Dog Column B Row 1: See Vet Row 2: See Vet Row 3: Get Rest Row 4: Get Rest Row 5: Get Rest Row 6: See Vet Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
View Replies!
View Related
Cell Number Format Based On Another Cells Value
If Cell A1 in my spreadsheet is 1, then cell A2 should appear formatted as a percentage. So if a user enters 9000 into cell A2, it should appear as 9000%. If Cell A1 in my spreadsheet is 2, then cell A2 should appear as a comma-formatted whole number. So if a user enters 9000 into cell A2, it should appear as 9,000. Conditional formatting doesn't appear to let me do this.
View Replies!
View Related
Format Cells Color Based On 1 Cell
I want to format a group of cells color based on the value of one referance cell. Example would be: If a1 is adn "X" the the results in cells b1 thru b10 would be green else they would be blue. Is there a way to do this using Excel 97?
View Replies!
View Related
Selecting Macro Based On Cell Value
I have a comparison macro that compares one sheet with the next, it is called sub comp1 (). There is a condition whereby columns must be removed before sub comp1 can run. To remove these columns I run the macro called sub colcut (). What I need is the following action: If cell BJ4 of current worksheet = "RE COMMENT" then run sub colcut () followed by sub comp1 () otherwise only run sub comp1 (). FYI The macro is to be run manually via a button rather than triggered by the value of the cell.
View Replies!
View Related
Formatting Row Of Cells Based On A Cell Content
I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person. I am almost certain that is going to take a VBA, but I know little to nothing about setting one up. I have attached a sample. I am using Excel 2003.
View Replies!
View Related
Vba Cell Control Based On The Cells Contents
Is it possible to place formulas in vba to control cells based on the cells contents. A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.# if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
View Replies!
View Related
Color Code Cells Based On Value In Row 1 And Value In Cell
I have 16 columns (B:Q) Row 1 contains either a number, the letter x, or the letter a. Cells in Range("B2:Q628) contains either the letter x or is blank If a cell in row 1 contains an x, then all cells in that column with an x has an Interior.ColorIndex of 45 If a cell in row 1 contains an a, then all cells in that column with a blank has an Interior.ColorIndex of 45 If a cell in row 1 contains has a number, then nothing
View Replies!
View Related
Protect Or Unprotect Cells Based On Cell Input
I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell? Here is what I am looking for: If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16 If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected. Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly. If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.
View Replies!
View Related
Format Color For Range Of Cells Based On Cell Value
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following: Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
View Replies!
View Related
Automatically Calculate 2 Cells Based On Cell Changed
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself). Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10. I really do not think the actual formulas matters at this time... Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
View Replies!
View Related
Changing Values Of Cells Based On Change Of One Cell Value
I am trying to do is to write a code that will change the values of cells B17:B25 to "false" when the user selects "true" from the drop-down box in cell B16. Here 's my Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "B16" Then If Target.Value = "TRUE" Then Range("B17:B25").FormulaR1C1 = "FALSE" End If End If End Sub This is not working! Nothing happens when I select "TRUE" in cell B16!
View Replies!
View Related
Give Cell A Unique ID Based On The Contents Of 2 Other Cells
I am trying to give a cell a unique ID based on the contents of 2 other cells. Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
View Replies!
View Related
|
TRACKER
