I'm trying to write code that will allow Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting using 5 conditions. I have the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:G10")) Is Nothing Then
Select Case Target
Case Is <= -9
icolor = 4
Case -8.9999999 To 8.999999999
icolor = 6
Case Is >= 9
icolor = 3
End Select
Target.Interior.ColorIndex = icolor
End If
If Not Intersect(Target, Range("A1:G10")) Is Nothing Then...............
The problem is I need to apply this formatting to a large chunk of cells that are already in a worksheet - or to be able to copy the cells into a new worksheet to be formatted alll in one go. Obviously I need to use something other than worksheet change in the code, but I don't know what....
I have created a named range that includes raw data layed out in 4 columns (Finish Name, Fill Color, Pattern, Font Color). On a separate sheet, when one of the finish names is selected in a particular cell, I want the cells fill, pattern, and font color to change according to the raw data. Below is the vba code I am trying to get to work. However, it is not working. It does not break, but does not achieve the desired result. It looks like the variables are getting set correctly, but still not changing the cell when they are referenced. I am only testing it on the first finish color, as seen in the code. All of the rest are static values for the cell, but I want the values to be dynamic.
Sub SetFinishSeparatorColor() ActiveCell = frmFinishSeparator.cboFinish.Value Set rngTarget = ActiveCell With Sheets("Pick List Do Not Tamper").Range("Finish_Cell_Color") lngFinishCellColor = . Find(What:=rngTarget, After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)..............
I have been able to put together the below which generates a random 15% sample of all records in the worksheet.
It works perfectly, however what I would ideally like to do is get it to only produce the 15% sample of rows where it is todays date in col B and has the word "other" in col C. I've tried lots of different IF statements and FOR statements, but am only generating blank worksheets.
Sub getrandomrows() Dim lr As Long, lc As Integer, a As Variant, MyDate As Date lr = Cells.Find("*", LookIn:=xlValues, after:=[a1], searchorder:=xlByRows, _
I am working on a challenging project. I have a list of names, job titles, and departments. Is it possible to select a random name who matches a specified job title or department? I'd prefer not to sort/filter/delete the original data range because the sheet will be used multiple times during the day by multiple users and with different criteria each time.
I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
I need contents in cell A5 to turn red based on 2 criteria occurring.
IFCell F5 is greater than 0 (zero) AND IF date in H5 is equal to or less than 30 from today's date. PS: if needed I have today's date in A1 for reference.
I am using pivot table for my customer aging which a sample is enclose. What I want to do is to Highlight the field " Customer Name " in colour if the the Field " Type of guarantee" is other than 0. Also a message of " Credit limit Exceeded" if the outstanding is more than the Credit limit.
1. Where there is data in column B 2. Goto first row of data 2. If column J = "Closed" 3. then select Cells from column B to J in that row and colour these cells in red 4. If column J= "Open" 5. the select Celss from column B to J in that row and colour these cells in grey 4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
=============================================================== Sub Colourclosed() Sheets("Risks").Select Dim LastRow As Long Dim i As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 8 To LastRow If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14 If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
I would like to create a spreadsheet that allows me to have a drop down list (in this case I diagnosis - from a list of shoulder, knee and ankle). I have used data validation to do this part. I would like to then have the next column automatically select the appropriate outcome measure (shoulder = DASH, Knee = Berg balance, Ankle =BPI) and display this next to the diagnosis and in the final outcome measure box. I would then like this to limit the possible entry details for the score boxes both at initial assessment and final (see D14, D15, D16 respectively).
I have attached a work sheet too - OP outcome measures.xlsx
How to convert any types format into Text with 5 digits in selection?
For instance, the content I select is "234", and I want it to convert to "00234". Just like the function "TEXT" in excel. How can I realize it promptly in VBA?
One worksheet will have a library of job elements for assembly process with standard times on it.For example:Pick up a bolt(0.621m),install gasket(0.231m) etc. The main worksheet will have a dropdown list which will list all the job elements discussed above.It will also have the quantity.So in otherwords,to pick up one bolt the time would be 0.621 minutes.But if anyone picks up 4 bolts the time would be 2.115 minutes,which can be calculated by the formula.So I want to display the job element,qty and the time in three separate rows.This worksheet will also calculate the total time for the assembly of the components which might contain more than thousand job elements. My question is how do I ensure that picking up any of these job elements will give me the correct time.Is this something that can be done by formulas or anything thats built in excel or I need to run a macro or VBA to run some codes for IF...ELSE....THEN statements.There are too many conditions like If pick up one bolt time is 0.621 minutes,if pick up 4 bolts time is 2.115 minutes or if pick up a gasket time is 0.231 minutes.
I want to do is randomly pull from a field of data, MINUS any cells that do not meet the criteria in other columns. So if column A is my target column for the random selection (containing 10 unique entries), and column B has 10 repeating colors, and column C has 10 repeating animals, what can I use to perhaps create this effect: A random pull from column A, but only from rows in which column B says red and column C says cat? (The pull being 1, 4, 7, or 9 in the below example, of course.)
Data (this example is also attached in an excel doc): One Red Cat Two White Dog Three Blue Horse Four Red Cat Five White Cat Six Blue Cat Seven Red Cat Eight Red Dog Nine Red Cat Ten Red Horse
I have tried variations on IF and INDEX to no avail. Perhaps something with SUMPRODUCT?
The VB for a macro to select only the rows/cells within an autofiltered selection. So if I Autofiltered this set of data by Column F where Lookup = NO it would only display Rows 3 & Row 7.
I would want the Macro then to select only these rows, then copy/paste them to another Worksheet called "Static".
************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF2F3F4F5F6F7F8F9F10=ABCDEF1Incident #Entry DateReceived Date# of days, Entry to ReceivedInst Act #Lookup2123456-102/16/0902/24/098A1YES3654321-202/17/0902/19/092A2NO4456125-102/17/0902/25/098A3YES5345678-302/17/0902/25/098A4YES6123456-202/17/0902/23/096A1YES7654321-302/17/0902/23/096A2NO8456125-202/17/0902/23/096A3YES9345678-402/17/0902/23/096A4YES10123456-302/17/0902/25/098A1YESSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst FROM CPS.dbo.uvVisit uvVisit WHERE (uvVisit.Date Between ? And ?) ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
I have multiple files in one directory. I have a master sheet that gives me basic information about all those files. In column K it list's all the file names and file paths. I would like to be able to use the auto filter to sort these files. Once the files are sorted they are opened and the relevant information (the CHRD page) is then copied into the master (Query.xls) the first copied page is renamed to sheet1 and second to sheet2 etc. Right now i have to manually run the macro's depending on which line the files are listed. I have also had to create separate macro's for each line.
I have a worksheet where I have restricted the users from scrolling around beyond a certain range.
Because of that the user is prevented from selecting an entire row. There are some cases where the user should be able to delete entire row or range of rows.
How can I allow them to select say cells "C17:C35" [they would just hoghlight the range] and hit ctrl+d; which would trigger a macro and the macro in turn would delete all the rows in that selection [rows 17 through 35]?
I know some basic codes for deleting entire rows, but I don't know how to read first and last row of the selection under Target property.
The list is generated from a table, which is hidden, like so:
Column 1 - Column 2 A - Text1 B - Text2 C - Text3
Now I want to have a very simple function that will allow me to COPY the text from Column 2 based on the selection from the drop-down list. For example, when the user selects "B" from the drop-down list, I want the text "Text2" to be copied to a specific cell.
I have a input selection, when i keyin XYZ, the data under column XYZ should be ranked in ascending order. If i select ABC then it should rank ABC data in ascending order.
I have a worksheet that lists the months of the year on row 3 from columns C through N. What I am trying to do is to be able to select the month I want from a drop down list or combobox and then hide the remaining columns based on the selection. For example if JAN is selected the months FEB through to DEC would be hidden.
Here is my ThisWorkbook.sheets("sheet1").Range("M").Select For Each cell In Selection If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then cell.EntireRow.Delete End If Next cell Range("a1").Select End Sub
I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
I have a dashboard that uses three pivots based off of a single data source. Each of the pivots is designed to work off of their own drop down list.
the first drop down list allows the user to select a lease date. The associated pivot will update with all servers assocated with that lease date.
the second drop down list allows the user to select a server. This list is limited by the lease date selected in the first drop down. The associated pivot will update with the partition information for the server selected in the drop down.
the third drop down list allows the user to select a partition. This list is limited by the server selected in the second drop down. The associated pivot will update with the detailed partition data for the partition selected in the drop down.
All of the above works, the issue is that when a new lease date is selected in the first drop down list, the current entries for the second and third drop down list are still reflecting the old lease date information. When you click the drop down the correct data is there, it is just not automatically populated.
Is there a way that when the user selects a new lease date in drop down one, the first server in the new list is automatically populated in the second drop down as well as the first partition in the third drop down?
