Search Entire Workbook for Certain Text, and blank out cells

I have a situation where I have a excel file that contains info that only certain people should be able to see All the Data. I decided to create a VBA script that when the file is saved, It saves Two copies of this file, one for un-privileged users to see certain data, and the original for privileged users to see everything.

After the File was saved for the Unprivileged, I need to scan through it and blank out the cells that they should not see.

What I have so far is this:

Please Login or Register  to view this content.

So, Im not sure if I am starting this in the correct way, but for what i have, I am stuck now on how to access a cell and read its value based on the row and column index.

Can someone give me a hand?

how about:

Please Login or Register  to view this content.

This one will change all the 9s in any of the sheets to "Redacted"

I don't know how many values you want to hide, but if you have a lot you could make an array and put another loop around the If statement:

Please Login or Register  to view this content.

Hi
try this

Please Login or Register  to view this content.

Thanks, that worked perfectly.

On a side note, Is it possible to sort an entire sheet by date in a given column, but only if the cell is not a certain color?
I know its possible by doing it manually, but if its done by script, will it have problems?

Not sure I understand what you're trying to do - if you're sorting the entire column, where do the coloured cells go?

Well I implemented what you suggested for removing the dollar amounts and it works great. I showed it to the Office worker that will be using it, and they asked me if it were possible to sort a certain column based on date. They do it manually as of now.

Its basically a shipping log. They color each Row Yellow for shipped items, and then other colors based on not shipped yet, late, etc.
What they do now is highlight all cells that are not yellow, then select sort by date from the Data menu so they can see what needs to be shipped and when.
So I guess i would need to know at what Row the Yellow cells stop, and then select everything else and then sort by date based on one Column.
Or if the Yello cells are scattered, I guess I would have to group all the yellow cells together, and then find the Row they end at, and then select everthing else.

I dont know if I explained it clear enough, do you understand what I am trying to ask?

Hi
you could do a sort on cell colour, then on value
this is a menu option under the "sort" tab

I was thinking about doing something like this by macro, but I ran into a problem.
The first three rows and column headers and merged cells.
When trying to sort by color the entire worksheet I am given an error about merged cells.
So is it possible to:
1: Select The entire worksheet, without the first three Rows selected? Then sort this by color.
2: Locate the beginning and ending cells of the sorted color so i can then select these, and sort by date?

I tried using the Macro recorder to see some of the commands it issues, but its always using Cell address, and for what I am trying to do, I will not know what cells are colored or not, etc