Changing the Pattern of a Cell(s)

[Jammer0816]

I have a message box that pops up reminding the user to put data in certain cells. When the user clicks on OK, is is possible to change the pattern on the cells in question? What I would like to do is change the pattern to yellow (hightlighter).

Thanks
This message was edited by Jammer0816 on 2002-08-27 05:52

 

[chef]

Format
Conditional Formatting
try Cells values >0 then format pattern to yellow.

regards
chef

 

[Jammer0816]

sorry chef...not sure i'm following you on this. Can you elaborate a little more please? I'm not a big Excel person as yet.

Thanks

 

[Jammer0816]

Ok...i see how the Conditional Formatting works now. How can I apply this to only occur after the OK button is clicked in the message box? I don't want the highlighted area to appear until after the button is clicked.

Thanks

 

[chef]

If you have VBA code that uses a message box then afraid my VBA is limited.

I have seen posts and know this can be done using code..someone else might pick up the thread.

Regards
Chef

 

[rikrak]

On 2002-08-27 05:51, Jammer0816 wrote:
I have a message box that pops up reminding the user to put data in certain cells. When the user clicks on OK, is is possible to change the pattern on the cells in question? What I would like to do is change the pattern to yellow (hightlighter).

Thanks

The fact that a box pops up tells me that there must be a sort of check:
IF Range("A1") = "" then MsgBox("..")

With same sort of test you check if the cell needs a pattern:
IF Range("A1") = "" Range("A1").interior.pattern = xlGray25

You can record a new macro, while formatting a cell, to obtain the values for pattern and/or color.

 

[Ian Mac]

On 2002-08-27 06:28, Jammer0816 wrote:
Ok...i see how the Conditional Formatting works now. How can I apply this to only occur after the OK button is clicked in the message box? I don't want the highlighted area to appear until after the button is clicked.

Thanks

Hi,

my VBA skills aren't that hot either, but try the following:

In the module you have the code for the message box put something like:

Sub Change_Colour()

Dim stS As String

stS = "A1,B2,C3:D17"

MsgBox "You need to complete the information in cells " & stS

Range("A1,B2,C3:D13").Select
Range("A1").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A1="""""
Selection.FormatConditions(1).Interior.ColorIndex = 6

End Sub

A1,B2,C3:D13 is the range of cells that you need, a comma separates different Cells or blocks of cells.

Notice that I've used activate A1 (When I recorded it conditional part it was C3), this will be the cell at the Top Left of the cells you need.

Also notice that the Formula1 also refernces that cell, change as required.

You cen dump the Message Box part I used it for testing.

Hope this helps,

 

[Jim North]

Here's my addition (not to be left out!)

ActiveCell.Font.Color = RGB(255,0,0)