adding text to two different cells and highlighting row

[lizsunnysideup]

Hello,

Can someone please help me with this? I am using a Dim statement in VBA but would like to expand my text to the next column over. Here's what I have so far:

Sub CommercialMarketPlace()
ActiveSheet.Select
Dim a As Long
For a = 1 To Cells(Rows.Count, "S").End(xlUp).Row
If Range("S" & a).Value = "DRUG NOT COVERED; INSTITUTIONAL DRUGS NOT COVERED" And Range("D" & a).Value = "” Or _
Range("S" & a).Value = "DRUG NOT COVERED; INSTITUTIONAL DRUGS NOT COVERED" And Range("D" & a).Value = “” Then
Range("D" & a).Value = "Exclude"
End If
Next a
End Sub

What I'd like the macro to do is additionally add the text "Ok" to the next column over to the right, so that every time there is an "Exclude" in column D, there is an "Ok" next to it in column E. I tried using: Range("D" & a).Value = "Exclude" And Range("E" & a).Value = "Ok" but this didn't seem to work. The system says Type Mismatch. Additionally, how would I highlight all rows that say "Exclude" to appear in yellow? And final question-I know I have an almost NON-EXISTENT understanding of how VBA works, but is there any online videos that one might recommend? There are a TON out there, so if there's a series that one might recommend over the others, would be so helpful!

Thank you so much for taking the time to read this, and hopefully responding.

 

[vds1]

Please post some example table in column S,D,E with expected result.

 

[Mentor82]

Hi
As far as it comes to your VBA code, try this.

Sub CommercialMarketPlace()
ActiveSheet.Select
Dim a As Long
For a = 1 To Cells(Rows.Count, "S").End(xlUp).Row
If Range("S" & a).Value = "DRUG NOT COVERED; INSTITUTIONAL DRUGS NOT COVERED" And Range("D" & a).Value = "” Or _
Range("S" & a).Value = "DRUG NOT COVERED; INSTITUTIONAL DRUGS NOT COVERED" And Range("D" & a).Value = “” Then
Range("D" & a).Value = "Exclude"
Range("D" & a).offset(0,1).value="OK"
End If
Next a
End Sub
[\CODE]

For the highligthning the row use excel conditional formatting.
Here are the steps to do this:

1. Select the entire dataset (A2:S1000 in this example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.Click on Conditional Formatting
4. Click on ‘New Rules’.
5. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
6. In the formula field, enter the following formula: =$D2=”Exclude”
7. Click the ‘Format’ button.
8. In the dialog box that opens, set the color in which you want the row to get highlighted.
9. Click OK.
This will highlight all the rows where the text in column D = Exclude.

As far as tutorials are concerned, I highly recommend you books written by John Walkenbach not only those which covers VBA itself but also those for getting to know excel.

Hope you find that usefull.
Regards,
Sebastian

 

[lizsunnysideup]

Hello again,

It almost worked. So the part in getting column E to say 'Ok' worked, but with the conditional formatting, the row directly above the cells that say "Exclude" are highlighted in yellow. Any suggestions on why it's not working?

Thanks again.

 

[lizsunnysideup]

Oh! And almost forgot to say thank you for the book recommendation! Thanks a bunch for that.

 

[Mentor82]

Hi,
My pleasure.
As fat as it comes to the conditional formatting look at entire data set range and the formula range (point 1 and 6). The entire range needs dollars sign before column letters and row numbers as below. Also if your entire data set range starts from for 2, as in the example below($A$2:...), then the formula range needs to have row number 2 withoutba dollar sign before the row # - the dollar before column letter is required ex. Formula (=$D2="Exclude")
1. Select the entire dataset ($A$2:$S$1000 in this example).
6. In the formula field, enter the following formula: =$D2=”Exclude”

 

[lizsunnysideup]

Hello again,

THANK YOU VERY MUCH! Thank you for clarifying, I thought I had selected the data set, but turns out I hadn't. And it worked! Thanks again for all the help, I greatly appreciate it

 

[Mentor82]

My pleasure. Thank you for a feedback