I have a list of electrical item brands in column B and a list of item types in column C what i need to happen is when i select a certain brand with a certain type i would like the cost of the item to automatically enter in to column D!
Because of my rubbish description i have attached an example
On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed.
The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.
On attachment, I am trying to highlight values in column AA dependant on any values appearing in range AF4: AQ8 less 15 minutes. eg at present the value in AA29 is 13:22. I would like this cell to highlight as it is greater than 15 minutes before AH4 at 13:25.
The purpose of the sheet is to highlight any times in column AA that fall within 15 minutes of the start of a major meeting. These major meetings are pulled from another sheet, and a drop down box is used on that other sheet that then transposes the major meeting times to this sheet. I have tried an "Or' statement in a conditional format, however an error message appears around not using a range in a conditional format.
I have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.
So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"
I have a sheet that contains two values in seperate columns that I need to concatinate into one value for the drop down. If I use one value my code works but doesn't show all the values. If I use both values I get a 1004 - Application Defined or object defined error. I bolded the section that if I add I get the 1004 error. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off.
Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer Dim intLastRow As Integer, intLastCol As Integer Dim strAdEvent As String, strAdEventNm As String Dim strAllAdEvent As String
Do Until intRow > intLastRow strAdEvent = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventCol).Value strAdEventNm = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventNmCol).Value If intRow = 2 Then..........................
Im writing a spreadsheet that will act as a hotel room booking diary. There will be 52 sheets (representing each week of the year) and each sheet will therefore represent one week
Each sheet will look at operate the same. Running horizontally will be the days of the week and running vertically will be each of the bedrooms. There are 22 bedrooms in total and each room will have 12 cells allotted to it for each day - lets call this a 'block' and presume that the first block runs from C10 to c21. The final of these cells C21 will have a validation and the user can select 'PAID' 'TO PAY' or 'INVOICED'
If the user selects 'PAID' in C21 I want cells C10 through to C21 to shade GREEN If the user selects 'TO PAY' in C21 I want cells C10 through to C21 to shade RED If the user selects 'INVOICE' in C21 I want cells C10 through to C21 to shade YELLOW
so basically, im trying to find a way to get cell G3 to have Value X dependant on value from Cell G2. e.g. if cell g2 is 8 and 9, g3=10 if g2 is 11, g3=20 and so on. i dont mind having to add a preset table of values if we need, but it would be set on sheet 2.
EDIT Value from cell G2 will always be between 4 and 20, set by the user. what i want is that each of those values gives G3 a pre-planned value. if g2 has value 4. g3 value would be -5 if g2 has value 5. g3 value would be 0 if g2 has value 6. g3 value would be 5 if g2 has value 7. g3 value would be 5 if g2 has value 8. g3 value would be 10 if g2 has value 9. g3 value would be 10 if g2 has value 10. g3 value would be 15 if g2 has value 11. g3 value would be 20 if g2 has value 12. g3 value would be 20 if g2 has value 13. g3 value would be 25 and so on until it reaches g2=20
i was thinking of setting a table on sheet 2 for the g3 values, and get an IF function to do something like IF g2 = a then x, if g2 = b then y etc etc. im just not sure how it would work, unless an INDEX function would work better?
I am trying to make an Excel sheet with a frontpage sheet and a data sheet. In the data sheet I have several columns with data and then in the end a cloumn with pictures. On the front page sheet I can easily with VLOOKUP formulas transfer the desired data from the data sheet to the front page sheet but I cannot seem to figure out how to copy the picture. When I try to copy the cell with the picture in it, the formula only copies the cell value, which is "0" because the only thing which is in the cell is the picture. how to copy a given picture from one sheet to another based on the information in another cell like a "Vlookup" formula?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then With Target Select Case .Value
Case "(None)": .Interior.ColorIndex = Null Case "One": .Interior.ColorIndex = 38 Case "Two": .Interior.ColorIndex = 18 Case "Three": .Interior.ColorIndex = 35 Case Else: .Interior.ColorIndex = xlNone End Select End With End If
My problem is that columns A to H are yellow(36 I think) and are merged cells. And Cells AW2 to BD2 are also yellow. The cells inbetween are white.
At the moment when the Cell value is "(None)" the cells turn white, I dont want this. I would like them to go to default.
i have a column with numbers in, in numerical order, however some cells are null. ie. 1 2
4 .. etc. I need to put paste images from a folder next to these numbers. (1.bmp next to '1' etc) and i need to leave the empty cells in. This code is sort of right..
Sub aids() Dim strPath As String Dim strFile As String Dim lngRow As Long Dim objPic As Object Dim sngMaxWidth As Single On Error Resume Next lngRow = 2 strPath = "C:images" strFile = Dir(strPath & "*.bmp") With ActiveSheet Do While strFile <> ""..........
I have a spreadsheet where I would like the rows to change colour dependant on value input to certain cells, i.e. if a any value other than a zero put into a cell it changes to green, if a zero put into a cell it changes to grey, if a zero put in but a value put in another cell it changes to another colour, if values already in are taken out of other cells, it changes to yellow.
I've looked on FAQ and there are a lot of similar things but not quire wha I'm after and even thn I dont know how to use VBA etc to achieve.
i have a order form that is customer dependant. the cust name on the form is manually entered by the user within a msg box. i have specific cusotmer folders on a shared machines C drive where i would like to save these excel docs to.
i understand that the cust name must match the name of the file and there needs to be a way to save those that do not have specific folder matching what was entered as the customer name.
the overall goal is to have the file name saved as CustomerNameDeliveryDateInvoiceNumber.xls where CustName is from cell "M3", delivery date is "Q7" and InvoiceNumber is "Q1".
how hard would it be to chane the background colour of a cell when a certain word is entered into it? so someone would type 'james' and press enter then the cell would turn red....would only be in a set cell range..
i have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
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.
I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.