Shading Cells Given Certain Requirements?
Aug 6, 2012
The logic is that I want the cells to be highlighted and have a border drawn for them as shown in the attached sheets (I have used record macro which will do this for rows 39 and 40 when the click button is clicked). The highlighting should start at the row number specified, stop once a merged cell is reached, skip the merged cell, continue highlighting, stop once a merged cell is reached, skip the merged cell, continue highlighting and so on and on until the last row (which is user specified) is reached. The first row after the merge cells should be highlighted in grey and yellow.
View 9 Replies
ADVERTISEMENT
Aug 30, 2013
I would like a cell to show either OK or No depending on the requirements of three other cells as below
G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.
I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)
=IF((G6+365)
View 2 Replies
View Related
Mar 7, 2009
I have a spreadsheet I use to keep my bank balance in...
I have this forum at the VERY top of it to show my bank balance:
=G3+SUMIF(H4:H2999,"X",E4:E2999)-SUMIF(H4:H2999,"X",F4:F2999)
Once an item clears, I put an X in column H, and then it updates the balance at the top to make it so I can reconcile easily.
What I want to do is have every line without an X in column H to show up a shaded color... any easy way to do this?
View 11 Replies
View Related
Mar 26, 2013
When I run a particular module of code that unhides several rows of a worksheet, revealing cells that are automatically populated and editable by the user, there are a few cells that are lightly highlighted in blue. This isn't to be misunderstood as background ... background shading goes righ to the 4 lines of the cell grid. This shading is a few pixels off the gridlines. Similarly, there are highlighted row numbers and column letters. The cells that are highlighted are a few of the cells that are locked for editting and are a result of a copied over formula. The highlighting goes away when you click on that cell.
One could almost say something is identifying the locked cells of the worksheet.
.Code:
Sub populate_existing()
Dim wshmain As Worksheet
Dim wshcore As Worksheet
Dim CRID As Long
Dim vlrange As Range
Set wshmain = Worksheets("Main")
Set wshcore = Worksheets("CONTROL_1")
CRID = wshmain.Range("B14")
Set vlrange = wshcore.Range("A:DZ")
[code]....
The cells highlighted are: G26, N26, X25, AE25, AE27, AL26, K42, R42, Y42, AF42, G47, N47, N49, G54, and N53.
I wasn't able to take a snapshot of the shading, it didn't show up in the Snippit tool image.
View 1 Replies
View Related
May 6, 2008
We have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.
The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.
The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.
how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?
View 9 Replies
View Related
Sep 23, 2013
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies
View Related
Oct 19, 2009
Need to summing a column if it meets two requirements. First it has to be (column A)type1 A and (column B)type2 W. if type1 and type2 are true, sum column C. I also attached a file that explains what i'm looking for.
View 5 Replies
View Related
Jan 5, 2014
What is my requirements in the attached snapshot and excel file .
Please check the attachment. Charts.JPG
View 1 Replies
View Related
Dec 23, 2008
In Column A I have vessel departure dates
In Column B I have Final Destinations
In Column D I have vessel arrival dates
I am trying to get the result of column D based on matching A & B information. My dilemma is that if I have vessels with the same departure dates going to different destinations how can I retrieve the arrival date? I've been trying "vlookup" and "index/match" to no avail.
View 4 Replies
View Related
Feb 5, 2010
I've been asked to produce a spreadsheet that monitors the time it takes to answer queries from partner companies. Ive attached some of the spread sheet that im struggling to work out. I would like a formula that will total the number of days it takes us to answer the queries per month for each company.
Example - For January
Compnay A 8 Days
Company B 7 Days
Company C 13 Days
View 3 Replies
View Related
Nov 11, 2009
I have a database where I need to count the qty of such item but it has to comply with two requirements. It will count only those that are similar to one of the cells but if the number is the same, not taking in count.
Example:
ItemLiablibity354A354A543A345A567A456B567B
So, I want to count all the items but only the one that matches the A on the column "Liability" but if it is repeated, not counted.
so the resul must be: 4 as one number is repeated.
View 9 Replies
View Related
Nov 17, 2007
I have drawn up a simple summary to show what I am trying to achieve: ...
View 9 Replies
View Related
Jun 30, 2006
I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, the name on this sheet will change daily, so you cannot use a "named" sheet in your macro. The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The data comes in such as:
MZ03948 134 126.23
PZ34533 128 12.28
IM04985 630 198.50
Z109384 40 35.65
2130494 76 12.28
2236326 12 45.63
5123132 12 12.23
Based on the FIRST TWO characters in the first column, I need to copy the data to the specific sheet. I would assume a simple LEFT() function would work for this. Here are the requirments for each sheet:
Esky - "MZ" or "51"
Indy - "IZ" or "Z" or "21"
Gfld - "PZ" or "22"
Other - Anything else
View 9 Replies
View Related
Dec 29, 2013
With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.
This is a two part process:
Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")
Here is part one
Code:
Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable
[code]...
Part one works perfectly.
Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.
I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.
As follows:
In particular: *E3 from Part 1*, etc.
Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)
Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and
Change
Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"
to the value of that nested if table
The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.
3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.
What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.
View 5 Replies
View Related
Jan 2, 2007
I have two columns. Date and Day
E.g.
Date Jan 1, 2007 and it falls on on a Monday in the Day column.
Date Apr 6, 2007 and it falls on on a Friday.
Date Dec 25, 2007 and it falls on on a Tuesday.
What I want is to Colour Code the DAY column in case it is a holiday falling on a Monday or a Friday.
View 9 Replies
View Related
Jul 13, 2014
I'm using the below code to shade rows, but right now it's only coloring the first cell in B12. I'd like to shade the row from B to the last used column
[Code] .....
View 8 Replies
View Related
Nov 7, 2007
I wish to fill cells with black colour for service days staff are not selectable.
The column Entitlement is a formula working on the year start date compares to to the employee start date to work out the days entitled.
What vba or formula(unlikely) do I need to achieve this?
******** ******************** ************************************************************************>Microsoft Excel - Holiday Allowances 2007.xls___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)boutH5H6H7H8H9H10=
ABCDEFGHIJKLMN1*Year*Start01/04/2007**********2OfficeNameNo.Start
DateWorking*Week*Entitlement12345*3****DaysHours********4**************56000Coley*B.27429-Dec-0117*2******66000Cox*D.A.2324-Jul-73537.5*5******76000Elsom*Lucy23304-Aug-0300*1******86000Graham*A.3919-Feb-65537.5*5******96000Graham*G.W.4001-Jan-8217*5******106000Hallam*K.A.15502-Dec-85649*5******Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Feb 4, 2010
I am trying to write vba code that will highlight the row in the range if a field is over a certain percent. The column number won't change but the number of rows will. I'd also like the code to automatically work on all tabs of the workbook when a button is clicked.
Data Info:
Currently there are 4 tabs, but can have more/less
Columns used are A:O
Data for shading starts at A3 and should go to however many rows have data and ignore blank rows
Formula should be if data in column E is over 10.00% then the data in that row A:O should be shaded in the color off yellow & have black thin orders
If the data in column E is not over 10.00% then the data in that row should not be shaded but still have thin black borders.
View 9 Replies
View Related
Jul 28, 2014
How do I shade a whole row based on the value in one of the columns?
View 11 Replies
View Related
Jun 21, 2009
At the moment i have print black and white so the cell shading does not print.
I also want to print a watermark but by having print black and white on the watermark is coming out white so you cant see it.
Is this possible so i can have cell shading on, but not print the cell shading and have a watermark that does print.
The watermark needs to be text not an image.
View 7 Replies
View Related
Jan 17, 2008
It's been about a year since i've used VBA and i need a quick piece of code that will Fill/Shade any cell in my worksheet Red (#FF0000) only IF the date is equal to Today or less...(I need to highlight expired cells)...
Im in Australia, and i know there's an issue between the Aus v US date in Excel...i believe there's a piece of code that can rectify this...
View 14 Replies
View Related
Dec 28, 2012
How do I in Excel 2003,2007,2010 shade in an enclosure I have drawn using scatterplot or some other line drawer with a grey darkness of my choice for that particular enclosure?
View 1 Replies
View Related
Oct 9, 2007
I am cross referencing products between two sheets. They come with different product numbers and I have modified them to search for the important last 5 digits from tab 1 (image 1 column D) with the full 11 digits in tab 2 (image 2 column E). The tab for cross referencing is named Distribution vlookup. I have the following formula and cannot get it to work properly. I have used this formula before in another similar report, and have adjusted the tab/file names and fields but I must be missing something since everything comes up as unauthorized, yet I can ctrl+f and find matching items.
VLOOKUP Formula:
=IF(ISNUMBER(VLOOKUP(D4,'Distribution vlookup'!$E$6:$E$133,1,FALSE))=TRUE,"Authorized","NotAuthorized")
Next, I wanted to nest a color coding on whether the item has sold in the last 12 months (image 2, column H). I cannot get that to work, so I tried to run a seperate column to identify selling status and cannot get that ot work either.
Sold in last 12 months formula:
=IF('Distribution vlookup'!H7>0,"Sold in last 12 mos","n/a")
View 9 Replies
View Related
Apr 29, 2014
I created a chart that was automatically assigned colors and shading that I like. I would like to use the same shading and color scheme for other charts (not the same chart type) however I cannot seem to figure out exactly what color and shade was automatically assigned (it is not one of the standard ones). Is there some way to copy the fill without copying the entire chart format?
View 2 Replies
View Related
May 21, 2008
In a workbook I have
Sheet 1 and Sheet 1a
1a is used to provide a start and finish date for a task - there are many of them
The following is located in a cell in sheet 1 which looks at 1a for the dates etc
HTML =IF(AND($A$427+B$430-1>='1a'!E10,$A$427+B$430-1<='1a'!I10,B$429<>"S"),"X","")
The end result is an X located in the respective feilds.
This works fine but.
I have set up the Calendar area in 1 so that when the X is shown it is Red.
The sheet also shows a Blank in both the SS [Sat and Sun]
What I would like to do is to have instead of the blank in the SS [Sat & Sun] a shaded grey which would clearly indicate that this was weekend.
What I am after is the weekdays with the X as now and the weekend only in a grey shade as it is at the moment without the X
View 14 Replies
View Related
Oct 24, 2005
Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it
shades the row blue. It would then be easy to visually tell in an excel
chart which lines belonged to ,say, specific companies.
View 9 Replies
View Related
Dec 26, 2013
I need a macro that will clear the contents of all cells shaded white in range c1:c20.
Clear the contents, leave the cells shaded white.
View 1 Replies
View Related
Jul 13, 2009
can i make a cell dark green if it is value 1 and very light green if its 10 and all varying shades of green in a linear fashion in between?
View 9 Replies
View Related
Nov 30, 2006
Is there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
View 7 Replies
View Related
Mar 5, 2014
I have a file that contains multiple rows of data. I built a macro that will then hide rows with a $0 balance. When the macro is completed, my spreadsheet is only showing the lines with a $ amount. (The $0 rows are hidden)
What I'd like to do is then format the unhidden rows so that each alternate row is color coded. This will separate the rows visually so I can more easily follow the rows across to view $'s per line.
I've attached a sample spreadsheet (it does contain the macro to hide the $0 rows). How can I then format the remaining rows with alternate shading?
View 4 Replies
View Related
