VBA script to color cells

[Tescatlipoca]

Hi and thank you in advance. I am looking for a VBA code where I can accomplish the following:

- Check for column A if column A has the text "abc" and "def" // column A can only have abc or def cant have both
- If found look at the row where the text "abc" or "def" exist and check the corresponding cell on column C (which is date) for the row abc or def
- If date exist on the corresponding cell on column C then gray out all cells above column C all the way to C2 as C1 is the heading
- if no value then ignore.

thank you!

[thomglea]

So if your data went to row 500, and on row 500 Column A is abc and there is a date in C500, you want C2:C499 greyed out? I think I am missing something here.

[kaasplankje]

Try this

Please Login or Register  to view this content.

[Tescatlipoca]

You are correct that is exactly what I am looking for

[Tescatlipoca]

Try this

Please Login or Register  to view this content.

Unfortunately the macro above did not work , I am pasting the image of sample data: Attachment 807654Attachment 807654

[kaasplankje]

Can't view your attachments for some reason but I am guessing that, as you are using excel 2010 (upgrading costs virtually nothing nowadays), some instructions are perhaps not understood.

[jindon]

If there are multiple rows that matches the condition, this will get the last matched.

If you want the first match, change max to min

Please Login or Register  to view this content.

[Tescatlipoca]

If there are multiple rows that matches the condition, this will get the last matched.

If you want the first match, change max to min

Please Login or Register  to view this content.

There is only one row matching the results, however when I ran the script it did not do anything no grayout no errors which was weird. in column A I have the task where I am looking for either abc or def and my date column is column D. I tried to update your VBA but no luck I also updated the spreadsheet to macth your VBA to show data only on column A and B but no luck either.

[jindon]

- Check for column A if column A has the text "abc" and "def" // column A can only have abc or def cant have both
- If found look at the row where the text "abc" or "def" exist and check the corresponding cell on column C (which is date) for the row abc or def

in column A I have the task where I am looking for either abc or def and my date column is column D. I tried to update your VBA

How did you update/adjust?

Upload sample workbook.

[Tescatlipoca]

test.xlsx

see attached. The only change I made to your VBA was to update the index 0,2 to 0,3.

[Tescatlipoca]

to clarify abc in this case is PSA/Lease Executed line 23

[jindon]

Your sample workbook is not like what you said in your opening post.
So, you need to explain what exactly you want in words, or upload your workbook with before/after sheet clearly showing what you are trying to do.

[Tescatlipoca]

Your sample workbook is not like what you said in your opening post.
So, you need to explain what exactly you want in words, or upload your workbook with before/after sheet clearly showing what you are trying to do.

Apologies, I summitted the wrong file on the previous message. Here is the correct before and after files.

before.xlsx
after.xlsx

[jindon]

See if this is how you wanted

Please Login or Register  to view this content.

[Tescatlipoca]

See if this is how you wanted

Please Login or Register  to view this content.

This macro changes the colors on the schedule, all I need is to search and find "PSA / LEASE EXECUTED" then color the corresponding date, in this case D23 to D5. Hope that make sense and thank you for all your effort...

[jindon]

change to

Please Login or Register  to view this content.

[Tescatlipoca]

change to

Please Login or Register  to view this content.

I do appreciate the effort but no luck again

[jindon]

Since it is working as you explained here, your excel is something wrong or upload the workbook that is not working with the code you are currently using.

[bebo021999]

Try:

Sub test()
Dim txt1 As String, txt2 As String, f
txt1 = "PSA / LEASE EXECUTED"
txt2 = "What is text 2?" ' apply 2nd text criteria here
Set f = Columns(1).Find(txt1)
If f Is Nothing Then Set f = Columns(1).Find(txt2)
If f Is Nothing Or Not IsDate(f.Offset(, 3)) Then MsgBox "No such data", vbCritical: Exit Sub
Range("D5", f.Offset(, 3)).Interior.Color = 13553360
End Sub