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).
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.
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...)
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?
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.
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..
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.
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)..........
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.
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 RelatedI 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
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?
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.
Creating border in different size at single attempt. Example as shown in the picture.
Excel Format.jpg
I'm trying to conditional format a cell that contains a formula that when the formula returns anything other than "" the cell contains a border.
My formula within the cell is referencing another worksheet
=IF(Sheet1!A1="","",Sheet1!A1)
Ideally the formula within the conditional format would be
=NOT(ISBLANK(Sheet1!$A$1))
but it isn't possible to reference another worksheet within a conditional format.
I am looking to create a macro that conditionally formats a cell depening on what words it contains.
if the cell has the word "level 1" in it = pink backround
"level 2" = red backround
"level 3" = Orange backround
"level 4" = Green Backround
"level 5" = Blue back round
i would like this to run on the active cell.
I am trying to work with conditional formatting using a macro.
How it works is I select the range I want to format manually then I run this macro.
It doesn't work
When I check the conditional format the formula is wrong. Instead of referencing the correct cell in columns E & F is is referencing E65282 & F65282
What do I need to change? ...
I have a complex sheet where rows are continuosly overwritten and new data needs to be formatted each time. My macro works great aside from the fact the conditional format formula expression adds double quotes rendering the conditional format useless. After running the macro I can go into the conditional format field, remove the quotes, and the formatting formula works as expected. So...I first did a "record macro" to grab the code for conditional formatting:
View 2 Replies View RelatedI am trying to record a macro for conditional formatting but when I check the VB Editor after I finish recording it, it only has
Sub CFTest()
'
' CFTest Macro
'
'
Range("D1:D4").Select
End Sub
How to get the keystrokes to record or what I need to do?
Reference with the different possible codes used in creating your own conditional formatting in a macro?
I need to conditionally format cells, based on a different column of cells having a particular letter, to have a black up or down arrow.
I'm trying to find a macro to apply conditional formatting to a large number of cells,
What I would like to do is when cell E96 has a value of a, cells E3:F95 are shaded in grey. Then when cell G96 has a value of a, cells G3:H96 are shaded in grey, and so on down to IU96 having a value of a and cells IU3:IV96 shaded in grey.
I've got a lovely little spreadsheet which looks like a more complicated version of this:
CaseResult1Pass2Fail3N/A4Pass (mark-ups)5Pass
I have conditional formatting on each cell of column A (titled Case) which looks at the next door cell in column B (titled Result), and if it turns out Pass, it colours the cell in A green. If it's fail, it colours the cell red, and so on.
So my spreadsheet, after formatting, looks like this:
CaseResult1Pass2Fail3N/A4Pass (mark-ups)5Pass
However, with the "Pass (mark-ups)" option, I've run out of conditions allowed (since I'm on 2003 excel). I can colour it in the same colour as other conditions, using the OR function, but I'd rather have it a separate colour.
I've got this macro here (which I've picked off the internet somewhere):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A5000")) Is Nothing Then
Select Case Target
Case "Passed"
icolor = 4
I am trying to record a macro for conditional formatting but when I check the VB Editor after I finish recording it, it only has ....
View 9 Replies View RelatedI have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following
Sub CellCCondFormatting()
Dim j As Long
Range("C2").Select
j = Range("C2"). CurrentRegion.Rows.Count
MsgBox ("1st: " & j)
Range("C2:C" & j).FormatConditions.Delete
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4
MsgBox ("2nd pass: " & j)
End Sub
It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................
Is it possible to record a macro or write VBA to apply conditional formatting to certain columns of cells where there are exceptions/conditions for the different types of conditional formatting? If possible, can those exceptions/conditions be based on a specific type of formula?
See my DATA EXAMPLE picture linked below. On that example, Columns K-Q need conditional formatting but manager wants row 38 to be blue based on the fact that it is using a =SUM() formula. This represents one "part" out of 75 that could be on a given sheet, each "part" has a different number of sub-parts that are used. So I can't just highlight columns K-Q because of the occasional SUM row that needs to be blue.
The conditional formatting that I am currently using in Columns K-Q only apply to K3:Q37 and not to row 38 at all. I currently have conditional formatting on columns K-N, P-Q where when '=ISNUMBER(xx)' returns TRUE is white and when it returns FALSE is light orange. Column O uses '=ISTEXT(xx)' for the same colors. Manager doesn't want to copy paste that formatting and wants it setup as a Macro/VBA.
DATA EXAMPLE - [URL]
RELEVANT INFORMATION
Columns K-Q need conditional formattingColumn K - Formula pasted in only on lines that require the Sales Price to show, will return a number value or error
(=VLOOKUP(B38,'SaleWS'!C:G,5,0).Column L - Formula pasted in every cell in column except L38 is '=IF(Hxx="G",IF(Exx"Description EX",VLOOKUP($Dxx&$Gxx,'PriceWS'!$D:$F,3,0),""),"")'.
L38 has a sum of all above valuesColumn M - Formula pasted in every cell in column except M38 is '=IF(ISNUMBER(Lxx),Ixx*Lxx,"")'.
M38 has a sum of all above valuesColumn N - Formula pasted in every cell in column except N38 is '=IF(Exx="Description EX",VLOOKUP(Dxx,'Material'!A:O,15,0),"")'.
N38 has a sum of all above valuesColumn O - No formulas here, just manual entryColumn P - Formula pasted in every cell in column except P38 is '=IF(H38="P",VLOOKUP($Dxx&$Oxx,'PressWS'!$A:$L,12,0),"")'.
P38 has a sum of all above valuesColumn Q - Formula pasted in every cell in column except Q38 is '=IF(H37="W",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),IF(Hxx="T",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),""))'.
Q38 has a sum of all above values.Rows where they are using SUM for all the rows above for that part need to be blue
I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
View 2 Replies View RelatedWhen I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
I have configured conditional formatting to check valid entries in a dependant list. I used the solution provided by SHG at:
http://www.excelforum.com/excel-prog...ant-lists.html
Now I need to use a macro to activate and deactivate the conditional formatting due to performance issues.
When the formula for data validation includes the indirect funtion I got a compile error and the macro won't run.
The macro firstly formats the data that is pasted into it from an access database, then it does some calculations to determine when 10 working days from a specific date is, and when 20 working days from the date is (these go in new columns at the end of the data). The macro will also add new columns which say "Not resolved" if there is no date in the Resolution column, "Yes" in the "10 working days met?" column if the condition is met and the same for 20 workings days in a different column. As the colouring etc takes a long time I really wanted to add as conditional formatting to the macro!
how to attach a spreadsheet here then I can show you what the outcome we want is. A point to note is that there is not a set number of rows each time we do this, and I don't just want 1 cell to be coloured - I want to specify which cells in the row are coloured depending on the results in another cell on that row.
After red filling certain cells based on some conditional formatting, I apply a filter to hide them. Now I need to delete them using a macro script.
View 9 Replies View Related