Mr Excel & excelisfun Trick 22: Conditional Formatting VBA or OFFSET?

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Dear Excel Team,

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

Hello all,

I'm trying to use conditional formatting to highlight values occuring more times than one in a column.

This is the rule I've applied on the range D:D with conditional formatting:
=COUNTIF(D:D;D1)=$A$1

In A1 the user can enter a number where the values occuring that number of times in column D:D are highlighted. No problems there.

The thing I would like to do now is to, somehow, let the user select what column to apply this to without changing the conditional formatting.

Eg:
In cell A1 the user enters 4 and the formatting highlights the values occuring 4 times.
In A2 the user enters 5 and the formatting applies on the 5th column - E:E.

Is this possible?


Regards,
milko

Hello everyone
I am using a worksheet and am trying to highlight differences in cell values between 2 columns,
column A and B.
So I highlight Column A, Format, Conditional Formatting,
Cell Value, is not equal to , =B1 , Format, Pattern, Colour.

Differences in cell values in Column A and Colmn B are thereby highlighted in the cells in Column A.

This works well, but what I would ideally like to do is refine, and use the Left offset function ( I think ! ) to ONLY identify where there is a difference between the first three characters in the cells in the two columns.
Example
Column A Column B
AB1 2AY AB1 2AZ
BB1 3AZ AB1 3AZ This line would be highlighted
AB3 3AT AB3 5TY

In other words, what syntax do I need to use to "offset"
Any answers would be most gratefully receiveed !
Pete

I have an excel spreadsheet that contains blocks of info in a 3x4 block. The block I need to use as the conditional format is the lower left cell. However, I do not know how to conditionally format the other cells based on if this cell is colored. Here is a visual:

A B C
1
2
3
4 Data

So in the conditional format I want A4='Data' color blue. I then want A1:C4 to also be blue if A4='Data'. Is Offset the best way to do this, or am I over thinking the problem? Also, I want the blocks to retain their colors when the scheduler moves them to a different sheet.

Hi everyone,

I have conditional formatting on B8:H8 based on contents of row 9.
So in B8 Formula is =ISNUMBER($B$9)
That works well but other users may add rows and copy formatting. Wondering if it's possible to add offset to the formula so that CF in each row is based on the cell directly below it.

If anyone could help that would be brilliant.

Cheers
Diddy

Hi Guys

I guess the title says it all, but a little more background:

2 columns - A = a list of products. B = a number

Ideally I would like to be able to use the arrow icon set on column A. It should be an upward arrow if the number in column B is positive, a downward arrow if it is negative and a horizontal arrow if it is 0.

But, i don't know if you can offset conditional formatting.

Thanks for any help

Neil

I can conditional format cells with dates but I have a problem offsetting it. This is my scenario; Column A has due dates (all of the same), Column B date completed and Column C to state Complete or Due/Urgent but what I am after is for Column B to have dates entered, and if the dates preceed or equal the due date, Column C will for dates that have preceeded or equalled the due date turn Green and say "Complete" and if later than the due date to go Red and say "Due/Urgent". I hope this is possible as itr will save a lot of typing, thank you for your help.

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0