Adding lines and conditional formatting with macros

Hi guys,

I am new here. I have only recently started playing around with macros, and am slowly getting into them. I do however still struggle a bit to ‘read’ them. Having played with formulas for years I can generally translate a string of formula text into English, like ‘if this cell value is greater than that cell value, then do this, if it is not then if it is equal to that cell value, do that, if not return 0’. Babbling like a child basically.

With visual basic I have managed to record some handy macros and then tweak them a little manually but I am still struggling to follow it going through it step by step reading it like a formula. So I hope you won’t mind me asking some very stupid questions. I mean well; I’m just a bit slow.

At present I am trying to do two different things on two different sheets, and I was wondering if perhaps one of you could nudge me in the right direction.


1:
I am trying to insert a blank row above every row that has a certain word in column B.

So basically ‘find value “Example” in column B, and when you find it, insert an entirely blank row directly above it’.


2:
Is a bit more challenging. I want to change the colour of a cell if the value in the cell corresponds with the value of another cell in the same column.

For example, I have a long list of surnames in column A. When I add ‘McNeil’ at the bottom, I would like to be able to run a macro that checks if the name McNeil appears anywhere else in column A, and if it does, that it changes the colour of the cell. Preferably both of the cells that say McNeil, but one would do very nicely indeed.

If that is possible, I wonder if it is possible to do the same with the first name in column B, but only if there was a match for the surname in column A on the same row. So, if McNeil does not appear in column A, don’t bother, but if it does, does the corresponding first name appear in column B?

If both of that is possible, the next step would obviously be if McNeil appears in column A (say twice, once in A123 and once in A678), do cells B123 and B678 match as well?


Would anybody be able to give me a shove in the right direction on either of these ?

I was also wondering if there is somewhere a list of macro functions, explaining how to construct them. Within the search function of Excel there are these help chapters for formulas, explaining the format and then what every part does. Is there a similar thing for macros?

I would be most grateful for any help you can offer, and am looking forward to getting stuck into macros a bit more.

Cheers.

Damien

Welcome to the Forum, please take the time to read the Forum Rules then use the Edit button to amend your Thread title.

Originally Posted by DamienC

My apologies

Please edit your title.

Also, this post has been reported to me for cross posting - http://www.mrexcel.com/forum/showthread.php?t=328358

This will add your new Rows

Please Login or Register  to view this content.

Why can't you use basic conditional formatting for the other requirement/

You can use Data validation to prevent duplicates. Select say A1 to A100 and then from the Data menu choose Data Validation.

From the Allow box in Settings choose Custom

In the box below type the formula as show below;

=COUNTIF($A$1:$A$99,A1)=1

Then add an apprptiate message in the Error Alert tab, eg

"You are entering a name that is already listed"

You will not now be able to enter an existing name, prevention is better than ure!

I think this des what you want. I have used a sheet event so it will be automatic

Please Login or Register  to view this content.

Right click on the sheet tab
Choose View code
Set the left hand drop down to WorkSheet
Find Worksheet_Change in the right hand dropdoen
Copy & paste the codinto the window

I put the comments in to help you.

If you leave the cells highlighted then subsequent matches won't stand out.

I thought that you allowed duplicate surnames, but not if the first name matched. So the code that I wrote checks for the first name & if it is found it then checks the surname. If both match the cells are highlighted. If there is no match the clls remain white.

Remove this code to keep the coloured cells

Please Login or Register  to view this content.

here's an example workbook using the original code.

That was pretty straightforward.

Then I think my last problem remaining is to not colour the actual cell I am clicking on. I presume:

Please Login or Register  to view this content.

is the area it looks for a match? Is it possible to change that to that range, but excluding the cell itself?

Amend these lines

Please Login or Register  to view this content.