- Thread starter Bonnieha
- Start date

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.

As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.

And about your question...

* If in a single Column's, all formula are same, and few are different, Excel trigger you the same by giving a Error Checking.. Please check on ..Excel > Option > Formulas > Error Checking Rules > Checked. or in VBA press Ctrl + G and write >

Code:

`Application.ErrorCheckingOptions.InconsistentTableFormula = TRUE`

* If you select Single Column.. (i.e. Column P) and then press Alt + E + G + S + M (Go to > Special > Column Difference), It will select all the cells which are not same as currently selected cell.

Try to play with both the option, hope it will help you in some case..

Regards,

Deb

I was also trying with this code can you tell me what went wrong with it: (Plz donot laugh while reading :D

[pre]

Code:

```
Sub CheckConsistentFormula()
Dim i As Range
Set i = Worksheets(1).Range("A1:B6")
For Each cell In i
If ActiveCell.Offset(1, 0).Formula = ActiveCell.Formula Then ActiveCell.Offset(1, 0).Interior = RGB(100, 10, 10)
ActiveCell.Offset(1, 0).Activate
Next cell
End Sub
```

Regards,

Faseeh

Two points :

1. It gives an error message at the statement : ActiveCell.Offset(1, 0).Interior = RGB(100, 10, 10) , which can be corrected by changing it to : ActiveCell.Offset(1, 0).Interior.Color = RGB(100, 10, 10)

2. It does not do what you think it should be doing.

Make the change shown in (1) and then you can start modifying it to do what you want it to do.

Narayan

* First of Thanks for your interest and welcome to the VBA Zone.

[list type=decimal]

[*]From Programming point of View:

1.very nice approach.

2.Just use one more level of hierarchy to set color

3.Try to avoid activate / Select for faster processing.

4.You have set I as

From Logical point of View:

1.Excel is a very popular and nicely coding world accepted and most advanced software.

2.In Programming, the hardest part is ERROR CHECKING, and excel is very much good in this.

3.It automatically adjust Cell references according to drag,

Code:

`=Sum(B1:B3) and [code]=Sum(C1:C3)`

[/list type=decimal]

*I would like to suggest to take help of EXCEL rather than adapt the same by personal codding.

[pre]Sub Macro1()

Selection.ColumnDifferences(ActiveCell).Select

Selection.Interior.Color = RGB(100, 10, 10)

End Sub[/code][/pre]

Regards,

Deb

Open a blank workbook and then go to Excel Options | Formula | check option 'R1C1 reference style'. And write simple formula in C1 to add A1 and B1 which will look:

=RC[-2]+RC[-1]

It means stay on the same row but look 2 and 1 columns to left respectively.

Now fill down this formula to C10. You will be amazed to see that formula stays the same. Now copy formula from C1:C10 to D1:D10 and still formula remains the same. This is how Excel probably interprets formulas internally for processing data.

Now go to Excel Options | Formula | uncheck option 'R1C1 reference style' and you will see that formulas are converted to what we see everyday i.e. A1+B1 in C1 and B10+C10 in D10. For us, it is easier to read A1 styled formulas.

So use ActiveCell.FormulaR1C1 [R1C1 styled notation] for comparison in VBA rather than Activecell.Formula [A1 styled notation] as it is evident that A1 styled notation will change for every cell though intrinsically formula is not changing.

Thanks you!

@ Deb,

Thank you for welcome. Your first point is clear. I have understood that. Point # 2: what i have understood that if i try to differentiate between cells on the basis of formulas then it will ignore changing cell references? Is that correct.

I do use the excel help but it will help some time to get expertise in it. I will keep asking until i completely grip it and the macro you write is working.

@ shrivallabha,

Well, it was a bit hard to digest, i am not familiar with R1C1 style working, neither i have found it in J Walk's book i am reading so i will have to practice it. I will revert with further questions, let me get acquainted with it first.

Thanks all,

I will revert with questions on some other thread.

Faseeh