question about a simple vba code

[ccsmile]

Hey, I have a question about how to write a vba program to realize such results:
I have two columns, one is the product name, another is the product price. For those rows with missing values in the second column, I need excel to color the corresponding first column with red automatically. Could u tell me how to write this simple vba code?

Hope I express the problem clearly. Thanks very much!

 

[Jonmo1]

Welcome to the board...

VBA is not needed, you can do this with conditional formatting..

The method will vary depending on which version of Excel you have..
I'm using 2003 here.


Highlight all occupied cells in column A
Make note of the currently active Row
This is shown in the Name Box to the left of the formula bar.

Click Format - Conditional Formatting
Select "Formula is"
Enter the formula: =$B1="" <--change the 1 to whatever the currently active row is.
Click Format - apply a red fill color
Click OK
Click OK

Hope that helps.

 

[Rick Rothstein]

The Conditional Formatting method that jonmo1 suggested is the way I would do what you asked for; however, if you really want to do this with VBA code, then here is a macro for you to consider...

Sub ColorColumnAforMissingPricesInColumnB()
  Const DataStartRow As Long = 1
  Cells(DataStartRow, "B").Resize(Cells(Rows.Count, "A").End(xlUp).Row - DataStartRow + 1).SpecialCells(xlCellTypeBlanks).Offset(, -1).Interior.ColorIndex = 6
End Sub

 

[ccsmile]

Welcome to the board...

VBA is not needed, you can do this with conditional formatting..

The method will vary depending on which version of Excel you have..
I'm using 2003 here.


Highlight all occupied cells in column A
Make note of the currently active Row
This is shown in the Name Box to the left of the formula bar.

Click Format - Conditional Formatting
Select "Formula is"
Enter the formula: =$B1="" <--change the 1 to whatever the currently active row is.
Click Format - apply a red fill color
Click OK
Click OK

Hope that helps.

Hey, thanks very much for your reply!
I did it in the excel 2007 by clicking conditional formating>-new rules.
But I was wondering if in this case, I have to do it row by row manually instead of making them produce all the results at one time, right? Thanks again!

 

[Rick Rothstein]

Hey, thanks very much for your reply!
I did it in the excel 2007 by clicking conditional formating>-new rules.
But I was wondering if in this case, I have to do it row by row manually instead of making them produce all the results at one time, right? Thanks again!

I think you missed this part of jonmo1's instructions...

Highlight all occupied cells in column A
Make note of the currently active Row
This is shown in the Name Box to the left of the formula bar.

If you do that before the last part, everything you do in the last part will automatically apply to all selected cells.

 

[ccsmile]

The Conditional Formatting method that jonmo1 suggested is the way I would do what you asked for; however, if you really want to do this with VBA code, then here is a macro for you to consider...

Sub ColorColumnAforMissingPricesInColumnB()
  Const DataStartRow As Long = 1
  Cells(DataStartRow, "B").Resize(Cells(Rows.Count, "A").End(xlUp).Row - DataStartRow + 1).SpecialCells(xlCellTypeBlanks).Offset(, -1).Interior.ColorIndex = 6
End Sub

Hey, thanks a lot for your anwers!
Could I ask a little bit more specific, because my data set is much complicated than I described, so I need to understand the code at first.
Could u tell me what does the first row and the second row in your code refer to?
I mean, is the first row meaning assign a constant value?
what does DataStartRow refer to?
What does End.(xlUp) mean?

Thanks again!

 

[ccsmile]

Hey, thanks a lot for your anwers!
Could I ask a little bit more specific, because my data set is much complicated than I described, so I need to understand the code at first.
Could u tell me what does the first row and the second row in your code refer to?
I mean, is the first row meaning assign a constant value?
what does DataStartRow refer to?
What does End.(xlUp) mean?

Thanks again!

 

[ccsmile]

could u tell me how could I "Make note of the currently active Row"?

sorry for the stupid question.-.-

 

[ccsmile]

i got it by just clicking. thanks.
but could u explain me a little bit about your vba code? thanks!

 

[Rick Rothstein]

could u tell me how could I "Make note of the currently active Row"?

i got it by just clicking

I'm not sure I understand what you mean by "just clicking". When you make a selection, all cells in that selection become "shaded" except for one of them... that cell is the "active cell"... the row it is on would be the "active row".