Macro that will hightlight a cell and four rows to right based on text in cell:

Hello All,
Here's what I'm trying to accomplish, I hope someone can give me some tips.

I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.

I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.

Anybody know what code I use?

It would basically say
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).

Thanks in advance, I can't beleive how helpful everyone is on this forum!

Why do you need macro? It can be made with CF
exmpl.xlsx

How it looks is perfect, thanks for posting that.

My only questions is- is it possible to incorporate applying the conditional
formatting into a Macro? I'm going to have very non-excel savvy people use this spreadsheet, and I'm concerned that explaining to them how to apply the formatting would be confusing for them. (I know how to build a Macro that will create the conditional formatting in a new sheet, I just can't get it to apply automatically).

Thanks again,

Chad

if you want apply CF automatically, set the macro code to Workbook_Open sub

Lancer102rus,
I can't get the Workbook_Open sub to work. I took the Macro I built that created the CF rules, and added "Private Sub Workbook_Open(), to the beginning, and tried running it. That didn't work. Again, it's creating the rules, but not executing them. Also, the CF rules don't want to keep the cells I'm applying them to saved (I checked the Macro Code to ensure the correct cells were selected, but it keeps defaulting to a different set of cells). I can't record a Macro to set the cells correctly.

Anyways, I've been trying the conditional formatting way for hours. Do you have any idea how I can use some VBA code instead? I don't really know how to build it, I'm usually able to find something similar online, and modify to my needs as necessary.

Thanks again,

Chad

HaHoBe,
Thanks for the tips. Do you have any similar code I can mess around with and try to repurpose?

This is probably a dumb question...does "behind the sheet" mean in the "source" section of the particular tab? Should I enter it into by adding a new module in the Visual Basic screen? Can I just copy and paste it into a different Macro that's not set to private?

Thanks for your patience,

Chad