Conditional Formatting For Row Banding Doesn't Work After Autofilter

Sep 8, 2009

For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.

View 3 Replies


ADVERTISEMENT

Conditional Formatting - Highlight A Cell When The Number Doesn't Match The Value?

May 11, 2014

In a column in which there are repeating numbers is there a formula I can enter into conditional formatting that will highlight a cell when the number doesn't match the value from the value above it?

So in this example the values with an * would be highlighted.

1*
1
1
2*
2
2
2
3*
4*
4
5*
5

View 2 Replies View Related

Excel 2003 :: Conditional Formatting Based On Application Of AutoFilter?

Dec 7, 2011

I was wondering if it is possible to have cells formatted based on the presence of an autofilter? I have a large worksheet, and to clean it up I would like to make the entire sheet (minus the category headers) invisible. Then, when the user applies a filter, the cells become visible.

View 1 Replies View Related

Conditional Formatting (the Cell In 'Work' Is Coloured Red, If Not Then It Is Coloured Green)

Mar 31, 2009

I have a workbook (see attached) that has 2 sheets, Work and Holiday. I want to be able to enter a persons initials into the wrksheet 'Work' for each day of the week.

If that person is listed on the holiday sheet for that day as 'A' then the cell in 'Work' is coloured Red, if not then it is coloured Green. I have tried to get this to work using a defined list and various IF statements but all to no avail.

View 2 Replies View Related

IF Function Doesn't Work?

Apr 25, 2014

i checked and checked and my IF function just gives me the wrong answer... attached is just a sample data..i have over 230 lines to check actually..

View 4 Replies View Related

Hyperlink Doesn't Work

Feb 5, 2010

I have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.

View 11 Replies View Related

Right Click Doesn't Work

May 19, 2008

As said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.

View 9 Replies View Related

Delete First Characters Doesn't Work?

Jan 22, 2014

See the excel sheet [URL]

I need to delete all the digits before / and also the / - in the valuta columns. But this =RIGHT(B2,LEN(B2)-6) doesnt work It says > the formula you typed contains an error

View 14 Replies View Related

Some Times If I Do A =if( ) Formula It Doesn't Work

Mar 12, 2008

What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?

Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.

View 11 Replies View Related

Macro Doesn't Work In 1 Out Of 4 Worksheets

Nov 30, 2012

i got a long macro to delete all rows with contains a column with the value 'xx'. The macro works in sheet 2K, 2F and 1G. But the weird thing is it doesnt work in sheet 1K (i copied the exact same part of the macro out of the other sheets). Is it possible that there are to many rows in the sheet (sheet 1K has 24.000 rows, 50% or more will be deleted with the macro. The error says bug in the line: If Not rng Is Nothing Then rng.EntireRow.Delete It stops showing all the rows wich should be deleted.

Sub GrondeigenarenVerwijderen()
Application.Goto ActiveWorkbook.Sheets("2K").Cells(1, 1)
Dim DeleteValue As String

[Code]....

View 5 Replies View Related

SUM / IF And LEFT Formula Doesn't Work

Jul 27, 2013

im trying to use below formula using SUM and two IF conditions and 1 left formula

Cond 1
Cond 2
Value

22
m9
10

23
m9
10

[code]....

the following formula works to capture the sum of combination 22 and m9:

{=SUM(IF((A2:A6)=A8,IF(B2:B6=B8,C2:C6,)))}

22
m9
20

24
m8
10

however, in my original table, the 22, 23 etc are values with 6 numbers as shown below

Cond 1
Cond 2
Value

2201
m9
10

2301
m9
10

[code]....

following formula didnt work;

{=SUM(IF(LEFT(E2:E6,2)=E8,IF(F2:F6=F8,G2:G6,)))}

22
m9
0

23
m9
0

why the secund formula didnt capture anything when i added left to the first one.

View 6 Replies View Related

VBA Doesn't Work When Sheet Is Hidden

Dec 25, 2013

I have below macro for Sheet2 and i have created a button on Sheet1.now whenever i press this button the macro will run and save Sheet2 as PDF file.

The problem is when i hide Sheet2 the macro doesnt work and it gives an error " Invalid procedure call or argument.

how to make this macro run even when the sheets are hidden ?

Sub PDF_Table()
'Sheet2.PageSetup.PrintArea = "$a$1:$x$140" '*****
'*** can remove the above line if sheet areas are already set and will not be altered
With Sheet2.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Zoom = 60

[code]...

View 2 Replies View Related

SUMIFS Statement Doesn't Work?

Jun 12, 2014

I can't get this to work. What am I doing wrong? I keep getting a #VALUE! error.

=SUMIFS($G$2:$G$31,$L$2:$L$30,F37,$B$2:$B$30,G$36)

I want to sum the NUMBERS in golumn G if the DATE in column L matches the DATE in cell F37 AND if the TEXT value in column B is equal to the TEXT value in cell G36.

View 2 Replies View Related

Why Doesn't My Variant Array Work

Jun 26, 2008

The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,

The code I have is:

Sub test()
Dim vSheetColours As Variant
Dim iCounter As Integer
vSheetColours = Range("Colours").Interior.ColorIndex
For iCounter = 1 To UBound(vSheetColours, 1)
MsgBox vSheetColours(iCounter, 1)
Next iCounter
End Sub

(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)

Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.

If I run this code, I get a runtime error 13 type mismatch, and it highlights
For iCounter = 1 To UBound(vSheetColours, 1)

But if I replace
vSheetColours = Range("Colours").Interior.ColorIndex
with
vSheetColours = Range("Colours").Value

it works fine.

View 9 Replies View Related

Full Screen Doesn't Work.

Dec 4, 2008

This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.

View 9 Replies View Related

Programme Doesn't Work When Sent To A Different Computer

May 5, 2009

I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying.

The part of the programme I'm having difficulty with is this:

Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

View 9 Replies View Related

Why Doesn't Escape Work While Sleep Is Running

Feb 3, 2014

Why doesn't Cancel (Escape key) work while Sleep API (kernel32.dll) is running?

I was looking at making a minor change to a solved thread (see [URL] ....)

I would like the user to be able to exit the form by pressing Escape. I altered the start and end of the sub to below but it didn't work:

START:

[Code]....

END:

[Code] .....

View 2 Replies View Related

2003 To 2007, VBA Codes Doesn't Work

Apr 18, 2009

I always used Office 2003 .. I just installed Office 2007 to try it out. But everything doesn't work in Excel 2007, which does work in Excel 2003.

I added an example of the list, but here are the codes:
Sheet1

View 2 Replies View Related

Sendkeys Doesn't Work On My Laptop With Vista

Jan 25, 2010

I have a workbook that I transfer back and forth from my desktop to my laptop, that uses Sendkeys to add code to the active pane in VBA. It doesn't work on my laptop, but works fine on my desktop.

Both computers have Vista. Is there a switch somewhere to make Sendkeys work?

View 7 Replies View Related

VBA Worksheet Name Check Macro Doesn't Work

Mar 10, 2012

I'm not sure why this macro in workbook "A" is not working when checking for the activesheet sheet opened in workbook "B"? I've included the tab name I'm looking for and the tab name opened in the workbook "B" in the MsgBox to verify it can pull the tab names properly in workbook "B" and it returns correctly. I'm not sure why the If Then Else is not working if that is the issue here.

Code:
Sub testmonthcheck()
Dim art156 As Workbook
Dim mm As String
Dim mmm As String
Set art156 = Workbooks("Actual_Run_Times_156.xls")

[Code] ........

View 1 Replies View Related

VBA Left Function Doesn't Work With Text?

Mar 23, 2012

HTML Code:
If Left(Range("H18"), 7) = A23C567 Or Left(Range("H18"), 7) = A65C321 Then
ActiveSheet.Cells(6, 7).Value = "Business and Private Banking"
End If

Im trying top use the above bit of code and it does not work when the criteria is Text , so if I simply change the above to =1234567 it works fine -- But I need it to be alpha numeric

View 6 Replies View Related

Why Doesn't Credit Debit Balance Work

Apr 6, 2012

Why doesn't a three column formula work?

Column C is Credit, D is Debit, E is Balance

I put the following formula in the Balance column

=E21+C22-D22

where the first term refers to cell immediately above. In other words, this formula takes the prior balance, adds a credit if any, subtracts a debit, if any, and gives a new balance.

The formula worked on the cell I put it in. It will not work when I copy and pasted it into the cell below the first one. Error I get is circular reference.

View 2 Replies View Related

Why Doesn't CONCATENATE Work In Graph Title

May 24, 2013

So, I'm trying to come up with a dynamic graph title. I highlight the graph title, go to the formula bar, and type:

Code:
=CONCATENATE(Sheet1!$C$1," All Servers")

"That function is not valid". Yet, the same function, placed in a cell, works perfectly. I suppose I could use an intermediate cell to hold the concatenated text and point the graph title to that cell, but I dislike having contextless cells like that floating around. Is there a way to apply that formula, or some other with the same effect, directly to the graph title?

View 4 Replies View Related

Autofilling Next Empty Row Doesn't Work (only Fills Out With 1)

Dec 9, 2013

I'm trying to create a code that autofills the next empty row in colum A with a number. What I have at the moment is:

'Create Index in Transaction list
x = Range("A" & Rows.Count).End(xlUp).Value
Sheets("Transaction list").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = x + 1

If I run this code in isolation from the rest of my code (which is copying and pasting values from one sheet to another) it works, but when I run the full code it only autofills the rows with "1".

View 3 Replies View Related

LARGE() Doesn't Work When Having Multiple Max Values

Feb 15, 2007

I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.

Example:
In this row:
7
7
6
5
5
4

I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.

How do I get the second value that I want?

Maybe if I use the RANK() formula in some way, but how?

View 9 Replies View Related

Doesn't Work If The Worksheet Hasn't Been Saved Yet

Jun 4, 2007

I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000.

2 reasons I'm doing this:

1) It doesn't work if the worksheet hasn't been saved yet.
2) It's ugly

=DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0)

The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it)

Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.

View 9 Replies View Related

Application.ScreenUpdating Parameter Doesn't Work

Jul 30, 2007

I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck.

View 9 Replies View Related

SUMIF Doesn't Work From Other Workbook When Closed

Aug 14, 2007

I have a sumif formula in one workbook that relates to information off of another workbook...when the other workbook is open the information populates no problem when you close the other workbook I get the #VALUE error. Is this typical of SUMIF when referring to another workbook? I have a vlookup function that refers to the same workbook and it works fine when the other workbook is closed?

View 9 Replies View Related

Find Method Doesn't Work With Some Text

Jul 24, 2006

I have a spreadsheet with data similar to the following:

12111000 MILK, COW'S, FLUID, LOW PERCENT
27313010 BEEF, NOODLES & VEG (W/ CARROTS/DK GREEN), NO SAUCE
11100000 MILK, NFS
11111000 MILK, COW'S, FLUID, WHOLE

If I use the following code to pass in a string variable and then attempt to find the string it never sees the text in line 2; or any line that contains (). The code is simply a test to try and figure out what i'm doing wrong. The actual goal is to search the entire sheet for text similar to that entered in by the user and then copy every row that contains similar text into another worksheet.

Sub CopyStuff()
strVariable = "car" 'I have tried "*car*" also
With Worksheets(1). Range("b1:b500")
Set c = .Find(strVariable, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Set c = .FindNext(c)
MsgBox (test)
Loop While Not c Is Nothing And c.Address <> firstaddress
test = c.Address
End If
End With
End Sub

View 9 Replies View Related

Error Handling Doesn't Work Properly

Jul 9, 2007

I've written the following bit of code but the error handling doesn't work properly: ....

View 9 Replies View Related







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