Hi
I need to create a macro which will compare the values in two columns and if values does not match then it should highlight those cells.
for example:
column A column B
1 abcd abcd
2 abce abcd
Commission Exception Discompare 11-16-2012.xls
Hi
I need to create a macro which will compare the values in two columns and if values does not match then it should highlight those cells.
for example:
column A column B
1 abcd abcd
2 abce abcd
Commission Exception Discompare 11-16-2012.xls
Last edited by jkmasurkar; 12-14-2012 at 12:03 PM.
can someone please help me with it!!
if i am correct, you want to compare 2 cells in the same row M and N.
you dont need a macro for this, you can use regular conditional formatting
1. highlight the range you want to apply the conditional formatting to
2. on the home tabe, styles, select CF
3. select new rule, select use formula
4. enter =$L2<>$M2 format fill yellow
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Do you just need to do conditional formatting, or do you really need a macro? This is easily done with built-in conditional formatting. But here is a macro
To do this with conditional formatting, use this formula and apply it to $D:$SWith Columns("D:S") .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(ROW(D1)>1,OR(AND(ISEVEN(COLUMN(D1)),D1<>E1),AND(ISODD(COLUMN(D1)),D1<>C1)))" .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With .FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = True
=AND(ROW(D1)>1,OR(AND(ISEVEN(COLUMN(D1)),D1<>E1),AND(ISODD(COLUMN(D1)),D1<>C1)))
hey i am getting this error when i try to run the macro. please check the screenshot below
and formula is also not working , please let me know if i am doing any mistake.
Here is a solution [jkmasurkar=conditional formatting when adjacent columns do not match=Commission Exception Disco.xlsm] that uses conditional formatting. It also includes the code if you want to try it. I did miss the End With somehow.
Hi Jazzer
Thanks for your help so far. i really appreciate it.
i got one error, I am attaching the screenshots with the message. please see if you can help me!
I have found the error but frankly there's no reason to bother with code. Just use the conditional formatting I set up. There is no advantage to doing this with code.
I tested this code and am seeing other issues with it also. The conditional format that is set turns out to be.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority ' should be .FormatConditions(.FormatConditions.Count).SetFirstPriority
=AND(ROW(XFC1048564)>1,OR(AND(ISEVEN(COLUMN(XFC1048564)),XFC1048564<>XFD1048564),AND(ISODD(COLUMN(XFC1048564)),XFC1048564<>XFB1048564)))
I do not know why the cell reference turns out like that; it's the lower-rightcorner of the entire universe, and it doesn't produce the right results.
when i try to use the formula , it just highlights right bottom corner cell even if they are identical
When you try to use which formula? The one in post #9 is an example of why the code is NOT working.
Just reference the file I posted in post #7. It uses the correct conditional formatting formula, as shown in post #4. That formula is specific to the SAFARI worksheet layout and allows you to use a single condition. If you try to copy it to other layouts it may not give you what you want. It assumes that data in the even-numbered columns will be compared to the data in the column immediately to the right, and columns to be compared are D:S.
The approach described by FDibbins above is much simpler but requires a condition for each pair of columns to be compared. If that is easier for you to work with you may want to try that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks