Bottom Border And Yellow Pattern In Condition Formatting

Jul 27, 2009

I am now trying this using Conditional Formatting in Excell 2000; so limited to 3 conditions. However I cannot get it work with multiple conditions:-

Values;-

A34 = Heritage Rly Assn Business Development Committee
A35 = North London Rly Historical Society
C34 = 23/09/2009 (formatted as WEEKDAY DD MMM YYYY)
D34 = 23/09/2009 13:30 (formatted as HH:MM)

In Cell C34

Format condition 1:-

=A34<>A35 , Format Bottom Border

Format condition 2:-

=AND(WEEKDAY(C34)>1,WEEKDAY(C34)<7,HOUR(D34)<18,A34<>A35), Format Bottom Border and Yellow pattern

Format condition 3:-

=AND(WEEKDAY(C34)>1,WEEKDAY(C34)<7,HOUR(D34)<18), FormatYellow pattern

In cell c34 Format condition 1 activates NOT condition 2.

In another cell, C13, condition 3 correctly operates; in this row A13 = A14. Condition 1 works i.e. the cells A1 and A2 are the same only cell A2 has the bottom border and if Cell A2 and A3 are different there is a bottom border in both cells. There must be something wrong with my formula in condition 2 but I can't see what; condition 2 is just condition 1 amd 3 amalgamated.

The condition 2 should read:

If the date is not a Saturday or Sunday in cell C34 and the time is before 18:00 hours in Cell D34 and the name in the "a" cell i.e. A34 does not equal the name in the "a" cell below this one i.e. A35 put in a bottom border and colour the cell yellow.

View 2 Replies


ADVERTISEMENT

Macro For Conditional Formatting Bottom Border

Jul 9, 2014

Possible to create a macro that would detect end page (jumping from page 1 to page 2 for instance) and add a bottom border at the last row of the page.

I have attached a sample where I highlight in green the end of the page and added manually the bottom border (I did it only for the first end pages).

View 7 Replies View Related

Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom

Sep 30, 2009

is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

View 2 Replies View Related

Using VBA To Add Border To Bottom Of Certain Cells

Jul 19, 2012

I need to underline certain cells based on the value of a cell in this case from column "E". Right now I run a macro to look at the value in each cell in column "E". THe macro then adds a certain amount of blank rows below the that row. So, if cell E1 has a 4, my macro runs a calculation that says 4 divided by 3, +2. So it would add 3 blank rows under row 1. It does this all the way down my column of data.

Now it gets tricky. To the right of column E, I have 6 blank columns, F,G,H,I,J,K. F,H, and J are spacer rows with no data. I need them blank with out any borders. Columns G, I, and K are the columns that I am wanting to add borders on the bottom of the cells based on the value in column E.

I need code to look at the value in E1, in this case 4, and add borders to 4 cells, in this case G1, I1, and K1 and then G2. If the value would have been 5, I need I2, 6 would add a border to K2 and 7 would add a border to G3. Catch my drift?

So, every cell in column E will not have a value because of me adding blank rows. So when there is a value, I need to add borders starting on the same row the value is in. At most, there will only be 3 underlined cells per row because of the format. Once the top row is filled, I need to drop down to the very next row.

View 9 Replies View Related

Bottom Border Depending On Matching Cell Value

Jul 25, 2009

I want to add borders to a range depending on the value in the "a" column. Normally I want a left border in cell "a", a right border in cell "h" and bottom border in cells "a" to "h". However if the value in cell "a" is the same as the cell below I do not want the bottom border.

Cell "a" contains the names of customers using our facilities each day. Some customers use several facilities on the same day. I want the borders to encompass all facilities used by each customer per day e.g. range "a1:h8" might need bottom borders on all rows, because they are single customers using only one facility that day but ranges "a9:h11" would require bottom borders only on row 11, because that customer is using three of our facilities that day.

Set Rng = ActiveSheet.Range("a:h").SpecialCells(xlCellTypeConstants)
change the Range to "a:h"
and:-
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)..........

View 5 Replies View Related

Apply A Bottom Page Border In Excel File?

Nov 6, 2012

I want to apply a bottom page border in my excel file. The page border should be fixed, i.e. if you insert a row above the border then the border should not shift as well.

View 2 Replies View Related

VBA - Adding Border Line To Bottom Row Of Set Print Area

Oct 15, 2009

I am using the following macro to set print area which I found on the net. I am trying to modify it as I would also like to place a border line on the bottom row but cant find anything to assist me despite searching through several threads.

Dim myrange As String
With ActiveSheet.Range("A:A")
myrange = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Address
End With
ActiveSheet.PageSetup.PrintArea = "$K$1:" & myrange
End Sub

View 9 Replies View Related

Excel 2010 :: Place Textbox Between X Axis And Bottom Border Of Chart

Oct 27, 2011

In Excel 2010 I need to place a text box between the X axis and the bottom border of a chart.

How can increase the white space between the X axis and the bottom border to allow me to do this?

View 1 Replies View Related

Add Border Line Dependent On Condition

May 5, 2013

I am trying to add several border lines to the bottom of a row from A to AI start from row 12.

There is probably a few things you need to know

1. Starting from row 11 each data section is two rows deep

2. The data section could be repeated and if this is the case do not insert border until it changes

3. The border line needs to be added underneath the second line i.e. to split the different types of data

View 5 Replies View Related

Conditional Formatting - Highlight Yellow If Cell Is Keyed In Instead Of Formula

Dec 17, 2012

I have a file where the cells are all formulas.

Employees are able to key in a number instead of using the number derived from the formula.

I want the cell to use conditional formatting without the use of macros, so it will highlight yellow if the cell is keyed in instead of a formula.

Is this possible? I have tried numerous things, but none seem to work.

Example:

Cell A6 has the number 24,248 derived from a formula.

Employee thinks the number should be 30,000 and keys that number in.

If it is 24,248, I want the number to have no fill, but if it is a keyed in number, I want it to be highlighted.

View 4 Replies View Related

Border Formatting In Excel

Nov 3, 2013

Creating border in different size at single attempt. Example as shown in the picture.

Excel Format.jpg

View 1 Replies View Related

Conditional Formatting Border Formula?

Mar 16, 2014

I have a list that, how do i create a border that data contains formula (formula not display/blank)...

i want to create dynamic border with condition if data display/showing must be border shown too...

for this, i want to line "TOTAL" in column AD expands up, based on data display/showing...

note : in cell AF2 as parameter/controller to show data by month (type 2 instead February or 3 --March etc...)

View 5 Replies View Related

Conditional Formatting - Thick Border

Dec 30, 2012

Border thickness limitations with conditional formatting.

I have a sheet that is formatted to fit on an A4 sheet. The text sizes are all defined to make it easy to read. I have two predefined border sizes on the sheet.

Thick for major section division
Thin for sections within the major section
The border color is Red regardless of thickness
There are cells that do not have borders

I have a need to change all existing border colors to Green when a cell has a particular value in it.

When I use conditional formatting, I only want to change the color not anything else but it always changes the existing thick lines to thin. This completely mucks up the layout because the row height and column width changes when the thick borders are changed to thin.

Is there any way at all to create a rule, or formula, or anything, that only changes the existing border color and nothing else?

View 1 Replies View Related

Conditional Formatting To Change Outside Border Of Range Only?

Oct 24, 2013

I'm trying to use conditional formatting to colour the border of a range of cells when another cell has data in it.

I can get it to color all the cells borders within that range when there's data in that cell but I just want the outside of the entire range.

View 4 Replies View Related

Fill Handle Pattern (not Finding My Pattern)

Jan 31, 2007

Pre-requisite: I would consider myself to be very poor with excel, based on what I've read on this forum and found on my web-searches. I have a worksheet that has a list of data on the left going vertically, then a summary of this data going horizontally across the top. It is not arranged in such a way that transposing the data will do what I want. I am pulling the 5th word out of the title of each block of the vertical data and need to show this word on the horizontal section.

When I use this formula to pull the 5th word: =MID(MID(MID(SUBSTITUTE(A2," ","^",4),1,256), FIND("^",SUBSTITUTE(A2," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2," ","^",4),1,256),FIND("^",SUBSTITUTE(A2," ","^",4)),256))-2)

I need to increase A2 to A30, then A58 (up by 28 every time) in every instance in that formula. The fill handle increases the values by 1, instead of 28 (even if I do 3 or 4 instances manually) How do I do this? I've run into this problem in other scenarios, and there HAS to be a way to get around it.

View 5 Replies View Related

Conditional Border Formatting Formula (after Repeat 5 Data)

Apr 9, 2014

how to make conditional formatting for border line (top & bottom) after repeat 5 data/text ....

it's possible with CF formula and do not vba?

see my excel file attached..

View 4 Replies View Related

Macro To Draw Border And Set Formatting In Pivot Table

Apr 13, 2012

I came across an issue on the pivot table after refreshing data. I always need to manually redo the border and formatting. I figure that it is because every time when some new group have move to another day, it change the pivot table layout again and so on.

1) I manage to draw border for Day 1, 2, 3, 4 and 5 but 'Beyond Day 5' i dont know how to do it.
2) Sometime some Day X will have no data then i will have problem with my script. (example pivot table doesnt show Day 2)
3) Possible to do looping for that?

I had attached a simple file.

View 6 Replies View Related

Conditional Formatting: If A Cell Value In Column X Contains The Word "open", Format Row Background To Yellow

May 7, 2009

I need to set up some conditional formatting on my spreadsheet, however I am having a little trouble with one of my conditions. The requirement is:

- if a cell value in column X contains the word "open", format row background to yellow.

- if a cell value in column X contains the word "resolved", format row background to green.

- if a cell value in column X contains the word "moved" OR "closed", format row background to blue.

Now I have manged the first two on my own, using the conditional formatting tool and using the formula "=SEARCH("open",INDIRECT("X"&ROW()))".

However I am stuck on the last one. I tried...

=OR(SEARCH("resolved",INDIRECT("X"&ROW())), SEARCH("closed",INDIRECT("X"&ROW())))

However this doesnt work. I tried looking at adding VB script in but to be honest I am not a VB programmer and cant really spend too much time on this. fix the final conditional format so it run if the cell contains either "resolved" or "closed"?

View 5 Replies View Related

Conditional Formatting :: Condition 1 Override Condition 2?

Feb 29, 2008

I'm having trouble with the two formulas working in conjuction with each other.

Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))

Condition 2:
=TODAY()>=A1

"Condition 1" seems to override "Condition 2"? Ever see that?

View 9 Replies View Related

Conditional Formatting - Change Bottom Sheet Number Color If It Exists On Top Sheet

May 25, 2014

First off I have an excel sheet that I have split into two windows. excel sheet.jpg

I am looking for a formula that will change the bottom sheet number a color if it exists on the top sheet.

View 8 Replies View Related

Formatting Until A Condition Is Met

Jan 26, 2009

I have the following code which prints the number of time steps required and displays it as t0, t1, t2 etc how can I make all the numbers subscript? i.e until the number of time steps = the total time.

View 2 Replies View Related

Condition Formatting By Percentage?

Jun 29, 2014

I would like to format a number of cells depending on the percentage of another cell.

I have a cell 'Seats" that has a number in it. In other columns, 'Stop1', 'Stop2', 'Stop3', are vaious numbers. I would like to format the Stop cells according to how close (or over) the number is that is in the 'Seats' cell. For example, if Seats is 30, Stop 1 is 15, it would be a medium red. Stop 2 is 30 (or over), it would be red. If Stop 3 is 5, then white. Basically a percentage scale of color depending on the number in the Seats field.

View 1 Replies View Related

Condition Formatting In Cells With No Value

Mar 1, 2010

I have a workbook (column K) set with the following format conditions:-

Cond.1 - =(I3="declined")
Cond. 2 - =(G3>0)
Cond. 3 - =(K3

View 9 Replies View Related

Conditional Formatting Under Changing Condition

Sep 13, 2009

There is a table [A8 TO D60] and a column alongside, where the months of the year are listed. In a particular independent cell, the name of any month can be chosen and entered.

The table rows preceding the chosen month, have to be shaded. When a different month is chosen, the shading should now cover the new set of rows preceding this different month and so on.

View 8 Replies View Related

Using Three Condition For Formatting Color Of Tabs?

Aug 21, 2014

I am using the following code that I got from this forum to color tabs. But I can only get it to do two colors. Is there a way to get three conditional colors of tabs?

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
Select Case .Range("D13").Value
Case Is < 20
.Tab.Color = vbGreen
Case Is > 20, Is < 30
.Tab.Color = vbBlue
Case Is > 30, Is < 100
.Tab.Color = vbRed
End Select
End With
End Sub

View 8 Replies View Related

Count By Conditional Formatting Condition

Feb 20, 2008

I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.

The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.

I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.

I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.

View 5 Replies View Related

Add Conditional Formatting Based On A Check Box Condition?

Aug 21, 2014

I would like to change the color of a cell to either green, yellow, or red based on a few conditions. One of the conditions would be based on whether a check box was checked or not.

View 2 Replies View Related

Condition Formatting - Viewing CF Formula At Each Cell

Jan 6, 2010

I'm referring to a post I tried to solve:http://www.excelforum.com/excel-gene...ml#post2227328

NO need to read. Try the attachement instead.

Steps done:
Select Column D
CF function =D1<A1
Select Column A
CF function =A1<D1

If I select cell D3 and look at the CF then I see:
CF: =D1<A1
where I would like to see
CF: =D3<A3

I remember this was the case in <=XL 2003? (now using 2010 beta)

View 4 Replies View Related

Disable Grey Out Condition Formatting In Ribbon

Sep 28, 2012

VBA code to disable(grey out) condition formatting command in ribbon. Because I generated a template and do not want user to change the condition formatting.

I have tried below, but it did not work...

Application.CommandBars(1).Controls("Format").Controls("Conditional Formatting...").Enabled = False

View 5 Replies View Related

Excel 2010 :: Condition Formatting On Column Of Text?

Jul 22, 2014

I have a column of numbers that are already formatted as text. Some of the data is duplicate which is fine. It's like a list of 6000 numbers. I want to conditionally format the column so that I can fill the cell with a color as long as it stays the same number, but if it changes to a new number make it a different color. I don't need a lot of colors. Two is fine. TI have Excel 2010.

View 4 Replies View Related







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