Conditional Formatting On Filtered Cells

Hi there,

I am currently using 'Activecell.Offset(1,0).Select' to move down one cell at a time when I click on a button.

The problem I have now is that if someone was to filter by something then the 'next cell down' could be hidden behind the filter (by that I mean it didn't meet the filter criteria).

Is there anyway to move down to the next row, even if that row does not follow on Sequentially .

Any help would be greatly appreciated.

Regards,

James

Hi,

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1
2
3
4

Fig. 2

1 1
2
3
4 4

Fig. 3
1 1
2 4
3
4

I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

[CODE]ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
'After filtering I want to change the value in column Y for only the filtered entries CODE]

Is it possible to apply a filter to multiple sheets within the same Excel workbook, using the same filter criteria?

For example, suppose you have tables of data on 3 different tabs, all having the same headings. Suppose one of the headings is "gender". If you apply a filter to the table on Sheet 1, to only show the rows containing the word "female", is it possible to have the tables on Sheets 2 and 3 automatically update to only show the "female" rows? Or would you have to manually update the filter criteria in Sheets 2 and 3?

I hope this makes sense. Thank you in advance for any help.

When I auto filter a spreadsheet, I can't figure out how to delete or
eliminate from the sheet the filtered-out rows. So when I refilter the sheet
with new items to get rid of, the old filtered items come back. I've tried
copying the range to another file, but I always get all the old data in the
new file.

Hi,

I have the following code to put data from a VBA userform into Excel

Dim Sh As Worksheet
Dim Rng As Range
Set Sh = ActiveSheet
Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)
With Rng
.Cells(1, 1) = Surname.Text
.Cells(1, 2) = forename.Text
.Cells(1, 3) = datein.Text
.Cells(1, 4) = origin.Text
.Cells(1, 5) = Addressee.Value
.Cells(1, 6) = usual.Value
.Cells(1, 7) = dateto.Text
.Cells(1, 8) = permission.Value
.Cells(1, 9) = dateseen.Text
.Cells(1, 10) = requestview.Value
.Cells(1, 11) = Invoice.Value
.Cells(1, 12) = notes.Text
.Cells(1, 13) = datecompleted.Text
.Cells(1, 14) = holdsend.Value
.Cells(1, 15) = fee.Text
.Cells(1, 16) = notes2.Text
.Cells(1, 17) = dateseen.Text
.Cells(1, 18) = invoicesent.Text
.Cells(1, 19) = Paid.Text
.Cells(1, 20) = Complete.Value

End With

What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.

Any help would be greatly appreciated!

Many thanks!

Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?

Hi,

I am using

ActiveSheet.ShowAllData

in a piece of VBA and it causes an error if the sheet does not have a filter applied! Is there an if statement I can use to check if the data is filtered?

Thanks - this site is an absolute life saver!

Rich

Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,
Randy

I recently moved into a new office and I am using a brand new computer on our network. When trying to edit an existing file, I'm not able to select a single cell and type. After clicking on a cell, if I move the mouse at all (even without pressing the left button) it just continues highlighting cells no matter where I move the mouse. I am also not able to click on the tool bars at all. I have to actually Ctrl-Alt_del to get out of the program. I'm sure this is something simple, but I don't know that much about this program. Any help would be greatly appreciated.

I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

I saw two threads in this forum that asked this question, with no good answer. I am posting this solution for anybody still struggling with this.

The question:
How can you prevent a cell's contents from overflowing into the next cell?

Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.

Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.

The solution:
Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).
Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.

Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.

Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.

Tested in Excel 2002

Is it possible to change the color of text using a formula, such as an IF
statement? What I have in mind is something like this (stated simply): If
cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4
red.

I would like to know if it is possible to deselect 1 cell after selecting
many cells using the CTL-Click method. For example, you are indivdually
selecting cells in a worksheet and you accdidently select a cell you dont
need, how can you deselect that one cell without deselecting all of them and
starting over again?

In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.

I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work

I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.


any Ideas???

Thanks