changing font color based on change actual number in a table

If your template was on the same worksheet you could use conditional formatting.

If your template was on a different worksheet in the same workbook (in the same cell locations in the example below) you could try a worksheet change event:

Please Login or Register  to view this content.

Please see attached.

Please see attached example for Conditional Formatting between 2 worksheets (using a dynamic named range instead of VBA).

A named range TemplateAddress has been set up in the attached example:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

With cells A2:G11 selected in the New worksheet (in the attached example) create a rule:

=A2<>TemplateAddress

...select your formatting.

Please see attached.

Originally Posted by pb71

Please see attached example for Conditional Formatting between 2 worksheets (using a dynamic named range instead of VBA).

A named range TemplateAddress has been set up in the attached example:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

With cells A2:G11 selected in the New worksheet (in the attached example) create a rule:

=A2<>TemplateAddress

...select your formatting.

Please see attached.

Yes thanks but in your example.....are 2 cells with brown color and are originals...how can I see all original workbook..whitout pattern ?

If you change the values on the New worksheet to match the values on the Original worksheet in the same cell locations (I changed the values for testing purposes) then the formatting will disappear.

Originally Posted by pb71

Please see attached example for Conditional Formatting between 2 worksheets (using a dynamic named range instead of VBA).

A named range TemplateAddress has been set up in the attached example:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

With cells A2:G11 selected in the New worksheet (in the attached example) create a rule:

=A2<>TemplateAddress

...select your formatting.

Please see attached.

ok but how i create a TemplateAdress ? this is first step..no?

Originally Posted by pb71

Excel 2003:

Select Insert - Name - Define

In the Names in workbook box, type TemplateAddress

In the Refers to box, type the following, and then click OK:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

(or whatever your reference worksheet is called - mine is named Original)

Excel 2007:

Formulas - Define Name

It is necesary to use two sheets? i cant work only on one sheet? Can you write to me each step please ?

Please post an example workbook so that I can understand your layout.

No, it isn't necessary to have two worksheets. You could have the original data in the same worksheet and you could use conditional formatting without having to use a dynamic named range.

You have one worksheet containing one table of data in your example. Where is the template that you are comparing to or have I misunderstood your original post?

[QUOTE=pb71;2336239]You have one worksheet containing one table of data in your example. Where is the template that you are comparing to or have I misunderstood your original post?[/QUOTE


Yes, but i operate on this tabel just for example. Do you say that i can use only a single sheet.
So after i createTemplateAddress with command: Insert-Name-Define and i selected all table...how i create a rule for first cell A1 ?


I attached a new exemple but i have a misteke sowhere i think....

Originally Posted by pb71

From your last example, I thought you had the original (template) table in the same worksheet.

The conditional formatting uses a formula that references the original table. Therefore, if all the cells in your new table had values and the referenced range (that you deleted) was empty, then all the cells in your new table would not be equal to the original and the conditional formatting would be applied.

How many rows and how many columns exactly? You have 256 columns and 65536 rows to play with in Excel 2003 so if you want the original (template) table next to the new table then as long as you have <=128 columns of values then you could still have the reference table in the same worksheet. If >128 columns then if you have <= 32768 rows of data (assuming no header row like your example) then you could have the original (template) table below the new table. If you didn't want the worksheet to look too cluttered, then you can always hide the reference table by hiding the columns or rows respectively.

I hope this helps.

Can you see please where is the mistake in this example ?

Originally Posted by pb71

You have a typo in the named range. Change to:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

EDIT:
Are you going to use 24 worksheets? You could always hide the reference worksheets once you have set up the conditional formatting.

I try to type
=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

but the formula contains error. I think is about equal......
What can i do?

I have used this in the attached example and it works for me.

Originally Posted by pb71

I have used this in the attached example and it works for me.

Yes . you're right.
Lets see the steps:
1. Selected all value and copy to a new worksheet.

2.Selected all value from original reference and create a TemplateAddress =INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN())) with command:
Insert-Name-Define

3.Selected all value from new worksheet and ...then go to the Conditionnel formatted and create a rule:

=A2<>TemplateAddress if A2 is in upper-corner of selected area.
Then formatting...format color of pattern of new values..


Where is my mistake pls ?

Please post the workbook where you are getting the error.

To clarify, using TemplateAddress:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

the tables must be in the same locations in both worksheets.

[QUOTE=shg;2336351]

ok shg, sorry..i am first time here on this forum. appologise.

Why i am getting error when i create a define name?

Exemple:

selected all table , then:

Define Name: name in workbook: TemplateAddress

refers to: =INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))
(in this case name sheet is : Original)

After that i am getting error and i cant create Define Name...

petri,

Please post the workbook where you are getting the error.

Originally Posted by pb71

petri,

Please post the workbook where you are getting the error.

ok. it is attached.

pb,

I receive this error: The formula you type contains an error.

Check the formula in your defined name TemplateAddress:

It currently reads:
="INDIRECT(""'Original'!""&ADDRESS(ROW(),COLUMN()))"

Change this to:
=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

As posted previously:

Excel 2003:

Select Insert - Name - Define

In the Names in workbook box, type TemplateAddress

In the Refers to box, type the following, and then click OK:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

(or whatever your reference worksheet is called - mine is named Original)

...have you tried to work with the example, TablesCompare.xls, that I posted previously.

In conditional formatting you would select your range first and then create the formatting rules.

[QUOTE=pb71;2336577]As posted previously:


Yes pb.
But i am getting error when i click ok.


I do exactly how do you say.

Can you post me a print screen?


In refers to: =INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

(mine sheet1 is named by me Original)


dont recognize that is a formula...i dont know why???????

I have update your example define.xls where you said you had the error. Can you work with this?

Have you looked at the TablesCompare.xls workbook that I posted?

Originally Posted by pb71

I have update your example define.xls where you said you had the error. Can you work with this?

Have you looked at the TablesCompare.xls workbook that I posted?

I fund where is the error:
In your topics do you wtite this refer:

=INDIRECT("'Original'!"&ADDRESS(ROW(),COLUMN()))

but in your examples after ROW() the sign is ; (semicolon) not
, (comma) .

I am sweathy...


pb cand you help me with a question about a multiple sum in all 12 sheet of workbook? I want to sum a sum from each sheet.

We need to move this toopic in another place?

Check your computer's regional settings, you have your list separator set to ;

Therefore, when writing formulas you will need to use ; instead of ,

Yes, it is a new question, so please start a new thread.

Yes, it is a new question, so please start a new thread.



Can you help me in this new post please?

Now i try to apply to create a templates with 12 table form each sheet of my workbook.

It is possible to copy in this new sheet (Templates) only values? Whitout formatting? and without borders? Becouse it is to difficult with all table....and text cells...

When copy a table in Templates to paste special only value.
After define name can i use conditionel formatting in this case?

Try it and see what happens. A good way to learn is to make mistakes (as long as they aren't fatal) and try to understand where it has all gone so terribly wrong.

If you post an example of your actual workbook explaining exactly what you want to achieve (maybe you could include some before and after worksheets) then this would help put things into context.

Ok, thanks, could you translate the headers for me - my Romanian is a bit rusty?

Please confirm:
1. Columns D to H are the columns that need to be checked for changes?
2. Can the number of rows in the table change?
3. Will the changes be confirmed against static values from this point forward or will you update the values to compare against going forward?
4. What is the reason behind highlighting the changes? Is it, for example, to assess whether more or less manpower is required if the areas/routes to clean increase/decrease significantly, in which case is a visual indicator of the change sufficient?
5. In which case, would you want different formatting if a value increases or decreases from the base line?

Etc.

[QUOTE=pb71;2338064]Yes, I'm in England.

There is no target. Only be observed on the ground surface of the street was cleaned and the area around lower street. Just observe changes. Just so .... We want to see the places where we changed ... so where was very clean ...

show what i managed to translate the tabel:

[QUOTE=pb71;2338064]

Hi.
I got an idea. To avoid mistakes Amendment values, I put two conditions in Conditionnal Formatting:
when you increase the value of a color and another color (or filling) when decrease the value.

Since such mistakes can be avoided by writing a value higher than the table. Can you help ? exchange as conditionnel formatting?

eg:
Condition1: =A1<Templates
Condition2: =A1>Templates

and then...set: formatting color font or filling.....every conditions

petri,

Please see the attached example. I haved added Data Validation to stop data values higher than the template being entered in the first place. The Data Validation also makes use of the same named range (per worksheet) as the Conditional Formatting. There are instructions on how to apply this in the attached workbook. Are we getting closer?

[QUOTE=pb71;2338708]petri,

How do I select data from table and two table cells below to apply conditional formatting? Can add (formula is) or just follow the format painter applied separately for the two cells after..?

I want to win time becouse I need to use often conditional formatting, then validation date....

Yes, format painter would work, however, note that the destination cell will take the other formatting of the source cell i.e. font, fill, borders etc. (and in your example the merged cells outside the table would be unmerged).

Alternatively, you could also first select the cells in the table and then, with the Ctrl button held down on your keyboard, click on the cells outside the table to include them in the selection.

Then, conditionally format making sure the last cell that you have selected is used in the conditional formatting rule. E.g. in the manual sweep rev1.xls example, on the CENTRU_I_sch.3 worksheet, first select cells D11 to H31, then with the Ctrl button held down, select cells D32 to E32 and then select cell E34.

The last cell selected is cell E34, therefore use

=E34<>centru1sch3

in your formatting rule and choose your formatting etc.

Then again, you could always select the cells outside the table one at a time and apply conditional formatting to them individually.

If you were using Excel 2007, you could edit the apply to range from Manage rules e.g.

Applies to
=$D$11:$H$31,$D$32:$E$32,$E$34

On a side note, in the manual sweep rev1.xls example, the data validation needs to be updated:
Ignore blank on the Settings tab needs to be unticked when setting the validation rule.

Originally Posted by pb71

Yes, format painter would work, however, note that the destination cell will take the other formatting of the source cell i.e. font, fill, borders etc. (and in your example the merged cells outside the table would be unmerged).

Alternatively, you could also first select the cells in the table and then, with the Ctrl button held down on your keyboard, click on the cells outside the table to include them in the selection.

Then, conditionally format making sure the last cell that you have selected is used in the conditional formatting rule. E.g. in the manual sweep rev1.xls example, on the CENTRU_I_sch.3 worksheet, first select cells D11 to H31, then with the Ctrl button held down, select cells D32 to E32 and then select cell E34.

The last cell selected is cell E34, therefore use

=E34<>centru1sch3

in your formatting rule and choose your formatting etc.

Then again, you could always select the cells outside the table one at a time and apply conditional formatting to them individually.

If you were using Excel 2007, you could edit the apply to range from Manage rules e.g.

Applies to
=$D$11:$H$31,$D$32:$E$32,$E$34

On a side note, in the manual sweep rev1.xls example, the data validation needs to be updated:
Ignore blank on the Settings tab needs to be unticked when setting the validation rule.

ok. Pb thank you,

l will do as you say in manual _sweet workbook example.
But what you say to uncheck 'Ignore blank' box in the Data-validation settings affect the empty cells. In empty cells, sometimes can adedd values and not restricting it. .
So I have to leave checked blank ignore Validation checked.

Ok, but I thought you said previously that the values could only decrease on the sheets. If there were any changes would these not be made on the template?

Originally Posted by pb71

Ok, but I thought you said previously that the values could only decrease on the sheets. If there were any changes would these not be made on the template?

Yes it is true. The value could only decrese but in two colomn (F si G) where i have empty cells...could be adedd some value only . Only in column F and G in each sheets, and only are empty cells. (so increase, it is an exception.

Need to change formula in Data Validation ?

Agreed, in this case there is no need to change the data validation.

petri,

Well done! If this does everything that you want it to, can the thread be marked as solved?

Is it time for the champagne?

yes it is time for champagne Pb.
Thank you a lot.
Can be marked as solved. I hope in future all will be fine.
Have a nice evening pb.
:-)