Conditional Format Multiple Shading
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
ADVERTISEMENT
Aug 17, 2009
I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?
View 3 Replies
View Related
Oct 27, 2008
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
View 9 Replies
View Related
Aug 18, 2006
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
View 10 Replies
View Related
Sep 19, 2013
I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:
Current Conditional Formatting Formula: =MOD(SUM(1/COUNTIF($a$2:$a2,$a$2:$a2)),2)=1
STATES (unfiltered)
AL
AL
AK
AK
AK
AR
AR
AZ
AZ
AZ
When I apply a filter, the rows remain shaded as they were originally:
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I want the conditional format to change when I have filtered out items (DESIRED RESULT):
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.
View 2 Replies
View Related
Feb 12, 2010
Need multiple conditional format for more than 3 condition. I don't know how to write code in VBA.
View 5 Replies
View Related
Oct 9, 2011
When I input in cell D3 with 1 and E3 with 7 or below, the cell of E3 will be red color. when I input in cell D3 with 1 and E3 with 8 and above, the cell of E3 will be green. When I input in cell D3 with 2 and E3 with 15 and below, the cell of E3 will be red color. when I input in cell D3 with 2 and E3 with 16 and above the cell of E3 will be green.
View 1 Replies
View Related
Feb 19, 2014
Is it possible to alternate how a condition works over many rows? Say column C has work order numbers eg. C2:C7 shows WO1234, C8:C15 shows WO1235, C16:C25 shows WO1236 and C26:C30 shows WO1237. What I would like to do is have rows 2:7 red, rows 8:15 blue, row 16:25 red, rows 26:30 blue and repeat to end (the range would be column A to column N. Ive used =MOD(ROW(),2) for every second row but am not sure how to get it to look at many rows and alternate as needed.
View 6 Replies
View Related
Feb 16, 2010
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.
View 9 Replies
View Related
Nov 14, 2013
I'm working on a criteria matrix in Excel 2010 that automatically plots a single member in a Scatter Chart based on the two values. There are 4 suppliers listed in Column D starting in cell D4 thru D7. The "x" value is listed in Column E starting in cell E4 thru E7. The "y" value is listed in Column F starting in cell F4 thru F7.
The scatter chart will plot the points correctly, however, there are two issues: 1) If I try to insert a data label using the "Series Name," or in this case, the supplier's name, it will lists ALL of the suppliers Column D. It will not list the single supplier listed in cell D4. 2) The scatter chart appears with gridlines as a 4x4 matrix with a total of 16 cells. The "x" and "y" axis both start at 0 and go to 4. I can shade the entire chart one color. However, I want to shade some of the cells with darker and lighter shades.
View 13 Replies
View Related
Jun 12, 2009
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
View 6 Replies
View Related
Mar 31, 2009
What I want to do is have a sub-Function that will check to make sure what is entered into a TextBox is a number, and if it is NOT a number that it changes the color of the TextBox to Yellow.
Currently if the entry is not numeric it just clears the cell and that part is good, but I want the color to flag that the user TRIED to put something there.
I'm doing this as a sub-Function since I need to use this check about 20-25 times.
View 6 Replies
View Related
Nov 5, 2013
I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).
I omit the "$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.
View 6 Replies
View Related
Apr 25, 2014
I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.
In the data below, items 4 and 13 would be shaded yellow.
I am using Excel 2010.
Priority A, B, C
Consumer UPC
Item Description
ACV Weighted Distribution
[Code] .......
View 8 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
Dec 19, 2006
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
View 9 Replies
View Related
Feb 9, 2010
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
View 2 Replies
View Related
Apr 12, 2012
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
View 5 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Apr 22, 2014
how to view changes on my spreadsheet.
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
View 2 Replies
View Related
Dec 16, 2008
I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.
I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.
View 4 Replies
View Related
Apr 24, 2009
Hi1Tasks23-Apr-09Inercona.Trial1
b.Trial2
Tasks23-Apr-09Enercona.Trial1b.Trial2
Disregard the First Table. I cant remove it. refer to the Second Table
This table is a Task Tracker. The Start Button Adds the Current Date in Enercon's Row C1 and the row below (C2) will automaticaly filled with color using Conditional Formating. My Question is how can I code a conditional format for Trail1 Rows. For example when Trail1 Cell is active then you click the start Button instead of Coloring D2 Cell it should Color D3 Cell.
Here is the Code in Start Button
Sub cmdStart_Click()
Dim i As Integer
Results = MsgBox("Are you working on " & ActiveCell.Value & " today ?", vbOKCancel, "")
If Results = vbOK Then
With Sheets("Sheet1")
i = .Cells(2, Columns.Count).End(xlToLeft).Column + 1
.Cells(2, i).Value = Now()
i = i + 1
End With
Else
Range("A1").Select
End If
End Sub
Regards,
Kevin
View 9 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
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
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
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
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
May 14, 2014
I would like to conditionally format a column of cells such that it formats when a cell's text equals to that in another column.
Example, in column A I have
1 Cat
2 Dog
3 Mouse
...
In B I have
1 Chicken
2 Mouse
3 Monkey
...
58 Cat
Based on the B column I would like to highlight A3 Mouse and A1 Cat.
Additional complexity, In B I have
1 Chicken Mouse Dog
2 Rat Monkey Fish
3 ...
Given that this is the format I have the text in how would I search column A and conditionally format A2 and A3.
View 4 Replies
View Related
