Macro to Conditionally Format matching column/row cells in a grid.

**Barking_Mad** · 06-26-2013, 08:32 AM

Hi,

I asked in the General Help thread about conditionally formatting cells as per the attached example. The original thread is here

So...when you read across and down rows and columns, I'd like the intersecting cell to turn green and contain "n" if the horizontal and vertical row columns letters are different. These letters will be entered in the horizontal row by the user. So in the attached, B5 is coloured green and contains "n" because B1 (value=a) doesn't match A5 (value=b) same for the other intersecting values.

Would it be possible to write a macro to re-create the above, with the macro running every time a value is entered in the rows B1:G1?

cf123.xls

**Mumps1** · 06-26-2013, 09:52 AM

Place the following code in the code module for your worksheet. This code will automatically adjust for the number of columns and rows that you use. You should remove the formulas you have in the cells.

Please Login or Register  to view this content.

**Barking_Mad** · 06-26-2013, 10:07 AM

Hi,

Amazing

That works perfectly in the demo. One small issue is that ive inserted some rows & columns so the cell locations are different, ive tried changing the code to fit, but obviously doing something wrong. Would be ever so grateful for an amended version!

.....

The cells across the top where the user enters the values are now:

M2:KX2

with the pre-existing vertical rows at:

G7:G1000

The intersecting cells that change are now:

M7:KX1000

**Mumps1** · 06-26-2013, 10:32 AM

Try this code. It will still adjust automatically if you add more columns or extend the range in column G.

Please Login or Register  to view this content.

**Barking_Mad** · 06-26-2013, 10:44 AM

Thanks, that's great

**Mumps1** · 06-26-2013, 03:27 PM

My pleasure.

**Barking_Mad** · 06-27-2013, 04:33 AM

Ahh, hit a snag - lol it doesn't quite work....My fault should have checked properly. I've attached an example sheet of the cells that are in use on my actual spreadsheet along with the code you used in the sheet 1 module. I realised that Cells(1, 13) should be (2, 13) as it's row B, but adjusting these doesn't seem to fix the problem.

If anyone else wants to sort it in Mumps current absence from the boards, please do

test.xlsm

edit: Id point out that when i delete letters from the top horizontal row it doesn't cause the letters in the main grid to turn back to white.

**Mumps1** · 06-27-2013, 09:01 AM

Try the attached file. I have modified the code so that if you enter data in row 2 and then change the entry, the cells in the main grid will be updated. If you delete letters from row 2, it doesn't have an immediate effect but as soon as you make another entry, the main grid will be updated. I hope this will work for you.

**Barking_Mad** · 06-27-2013, 09:48 AM

Mumps, thanks, very appreciative of your time in helping me with this.

Works perfectly, the only problem ive found is that any cells previously containing text (and therefore that are also conditionally formatted) are blanked out when the macro runs

**Mumps1** · 06-27-2013, 10:21 AM

If that's a serious problem, could you post a copy of the file that shows those previously populated cells?

**Barking_Mad** · 06-28-2013, 05:46 AM

Thanks. Please see the attached zip file. The original entries aren't in, but if you put an X in the grid, you'll see the cells turn red. If the GROUP cells don't match, they will be turned white and erased...The two columns that change (HECS2183 sheet) are the ones headed GROUPS, although the vertical ones is populated first with student names, and then when the student absences are entered in the horizontal part each group goes in one at a time.

test macro.zip

ps for info: all the names in the sheet are randomly generated and not real

**Mumps1** · 06-28-2013, 10:35 AM

I have been looking at your file and I think that the conditional formatting that you set and the macro may be conflicting with each other. I've been playing around with it and removed most of the conditional formatting that you set. Please try it out and see if it does all or most of what you wanted. I'll be away for the next week so I won't be able to respond to your thread during that time. However, someone else may be able to help.