Cell Shading Code

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


ADVERTISEMENT

Cell Shading By Day

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

Shading One Cell On Value Of Another

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

Cell Shading And Watermark

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

Cell Shading With Incrementing Values

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

Cell Shading Macro Depending On Text

May 30, 2008

I will have a spreadsheet similar to the image provided. Basically, I need the macro to go row by row down column I. If the cell in column I contains the word "Tech" I need the cells from column B to P to be shaded color1; if it contains "Update," color 2; if the cell is blank; leave the cell as is. Once the macro has done this, it will continue to the next row and to the same check.

The number of rows in the spreadsheet will change on a day-to-day basis depending on the data that's pulled, but the columns should remain the same.

So I think I have the shading part. This is what I have so far. I'm not sure if this is the most efficient way of coding it, but it kinda works. I guess another problem would be if in the future the column with this info (column I) changes and shifts.

View 10 Replies View Related

Changing Font Color With Cell Shading Vba

Jun 14, 2007

"I want to make a spread sheet where the "shading" changes based on the text entered into the cell. ie I want to be able to enter yellow into a cell and have the cell shading change to yellow."

THis was accomplished with the following ....

View 6 Replies View Related

Shading Cell Based On Ranges Of Numeric Data

Jul 10, 2014

How do you shade a cell based on multiple ranges of data? e.g. A value of...

1 shades red
2-3 shades orange
4-5 shades yellow
6 shades green

View 4 Replies View Related

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

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

Shading Of Cells With No Value?

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

Row Shading In Used Range

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

Odd Shading Of Cells?

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

VBA For Shading And Borders

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

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 View Related

Shading Whole Row Based On Value In One Column?

Jul 28, 2014

How do I shade a whole row based on the value in one of the columns?

View 11 Replies View Related

Shading Chart Subareas?

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

VLookup And Color Shading

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

Copy Only Fill / Shading From One Chart To Another?

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

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 View Related

Auto Color Shading Of Rows

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

Clear Range Based On Shading?

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

Shading Alternating Rows In A Range

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

Shading Alternate Unhidden Rows In Excel?

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

Color Shading Options In Excel 2003

Feb 5, 2010

In Excel 2003, when choosing colors for cell patterns, it only gives me the option of 40 colors. How do I get it to offer me the "More colors..." that you see when coloring shapes that brings up the hexagonal color pallette?

View 9 Replies View Related

Excel 2010 :: Multiple Shading Within A Scatter Chart?

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

Conditional Format Shading Stop Updating Until Save...?

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

Conditional Formatting Similar To Shading Alternating Rows

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

Shading Area Under Lowest Series In Line Chart

Oct 2, 2006

I am creating a line chart with markers using 5 series of data. I want to colour in/shade the area under the lowest series.

View 6 Replies View Related

Excel 2010 :: Conditional Formatting - Shading By Group When Filtered?

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

Conditional Format: Alternate Row Shading Based On First To Letters In The A Column

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







Copyrights 2005-15 www.BigResource.com, All rights reserved