PDA

View Full Version : Solved: If cell = "" then highlight cell



Mitchelson
05-20-2008, 09:05 AM
Hi All,

I'm using the below code (Supplied by xld :bow: ) to enforce mandatory fields before the work closers.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String

With Worksheets("Tickets")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To LastRow

tmp = ""
If .Cells(i, "D").Value = "Timesheet" Then

If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K").Resize(, 1)) < 3 Then

tmp = "Completed all Timesheet cells before closing workbook" & vbNewLine
End If
End If

If tmp <> "" Then
msg = msg & "Row " & i & " value = " & .Cells(i, "D").Value & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With
End Sub



Is it possible to insert into the code if the cell equals "" then hightlight the cell that is missing the data and then once filled in to un-highlight the cell?

Or should I use a separate Sub highlight all the cells that I want filled in when the user selects "Timesheet" in column D and un-highlight once the value is great than 0?

MikeO
05-20-2008, 09:22 AM
Have you tried conditional formatting?

Simon Lloyd
05-20-2008, 09:29 AM
This will select all blank cells in your range:

With ActiveSheet
On Error GoTo Nxt:
.Range("E5:K" & Range("K" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Select
Nxt:
End With

Mitchelson
05-20-2008, 10:04 AM
Have you tried conditional formatting?
Yes, I already use a large amount of conditional formatting. By defining the name e.g. EURC={"AL / Sick","Fault","MAC","Other","Projects","Timesheet","Task"} and using the conditional formatting =MATCH($D10,for_,0).

But what I can't get if cell = "EURC" then highlight and if cell > 0 then no colour.

I really would like to move all the conditional formatting to VBA but still just learning.




With ActiveSheet
On Error Goto Nxt:
.Range("E5:K" & Range("K" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Select
Nxt:
End With


Thanks for the reply Simon. still have to test your code

Mitchelson
05-20-2008, 01:35 PM
With ActiveSheet
On Error Goto Nxt:
.Range("E5:K" & Range("K" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Select
Nxt:
End With

With Selection
Selection.Interior.ColorIndex = 6
End With The selection works well but it only select the cells with missing data in row 5. How can I get it to select the cells with missing data any row where is finds the text "Timesheet".

By adding the With Selection statement you can highlighted cells with the missing data.

Simon Lloyd
05-20-2008, 02:49 PM
It will only select on row 5 if the last used row in column K is 5, i worked it on 40 rows in my test with 15 blanks over the range of rows and it selected all of them!, how can it find missing data if there is already "Timesheet" in the cell?

Mitchelson
05-20-2008, 03:05 PM
I realy sorry, I really should of attached the workbook with example. I'm replying from a iPhone now.

Will attach the workbook first thing tomorrow.

Thank you for your time and help.

Mitchelson
05-20-2008, 11:41 PM
Morning All,

Please find attached an example of the workbook I'm using.

8789

At the moment the code looks in column D to see if there is a "Timesheet" then it highlights any missing data in E5:K5. If you enter a second "timesheet" in column D e.g. in D9 the code will still only select missing data in Row 5 but I looking for the missing data in Row 9.

Many thanks

Simon Lloyd
05-21-2008, 02:31 AM
This will check column D for entries and if found check the row from E to K for blanks, if found will select them!

Sub Select_Blanks()
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("D5:D" & Range("D" & Rows.Count).End(xlUp).Row) _
.SpecialCells(xlCellTypeConstants)
If Range(MyCell.Offset(0, 1).Address & ":" & MyCell.Offset(0, 7).Address) _
.SpecialCells(xlCellTypeBlanks).Count > 0 Then
MsgBox "missing data"
Range(MyCell.Offset(0, 1).Address & ":" & MyCell.Offset(0, 7).Address) _
.SpecialCells(xlCellTypeBlanks).Select
End If
Next MyCell
End Sub

Jow
05-21-2008, 03:47 AM
My idea is this...it will highlight the blank cells within the selection



Selection.SpecialCells(xlCellTypeBlanks).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Then if you put in a bit of code to repeat that everytime an entry is made, it should constantly be updating the formatting of the empty cells...

Simon Lloyd
05-21-2008, 03:51 AM
Jow, the Op doesn't want to highlight all blanks just when there is an entry in column D.

Mitchelson
05-22-2008, 08:49 AM
This is code I using now but this having a few problems.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String


With Worksheets("Tickets")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To LastRow

tmp = ""

If .Cells(i, "D").Value = "Timesheet" Then
If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K") _
.Resize(, 1)) < 3 Then
End If
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("D5:D" & Range("D" & Rows.Count).End(xlUp).Row) _
.SpecialCells(xlCellTypeConstants)
If Range(MyCell.Offset(0, 1).Address & ":" & MyCell.Offset(0, 7).Address) _
.SpecialCells(xlCellTypeBlanks).Count > 0 Then
Range(MyCell.Offset(0, 1).Address & ":" & MyCell.Offset(0, 7).Address) _
.SpecialCells(xlCellTypeBlanks).Select
tmp = "Completed all Timesheet cells before closing workbook" & vbNewLine
With slection
If Selection.Value > 0 Then
Selection.Interior.ColorIndex = 0
ElseIf Selection.Value = "" Then
Selection.Interior.ColorIndex = 6
End If
End With
End If
Next MyCell
End If

If tmp <> "" Then
msg = msg & "Row " & i & " value = " & .Cells(i, "D").Value & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With
End Sub


Firstly, I want to insert an if statment into the code so if the code finds a blank cell it highlights the cell in yellow and once data has been entered in the cell for the colour to be remove.

Secondly, the code selects the cells from column D to column K. How can I change that to select cells in the following range ("D:E", "I", "K")

Simon Lloyd
05-22-2008, 08:54 AM
You keep changing the goal posts!, you didn't even post back to say the code i gave you worked!

As for the first thing you want doing just change the select for the interior colour index and for the second experiment a little with the ranges!

You also need to check for typo's
With slection

Simon Lloyd
05-22-2008, 10:16 AM
.....As an also,

Selection.Interior.ColorIndex = 0

should be

Selection.Interior.ColorIndex = xlNone
as setting a colour (0=white) will increse the size of your workbook!

Mitchelson
05-22-2008, 11:38 AM
Simon,

I must apologize for taking so long to reply.

With Work and Family life, I've got limited time to spend with trying to learn the newly found VBA. In the future I'll leave a quick post saying that I working on the code and will reply once I have the results.

Once again thank you for all you time with helping me learn VBA.

Simon Lloyd
05-22-2008, 11:41 AM
Simon,

I must apologize for taking so long to reply.

With Work and Family life, I've got limited time to spend with trying to learn the newly found VBA. In the future I'll leave a quick post saying that I working on the code and will reply once I have the results.

Once again thank you for all you time with helping me learn VBA.????? Well did it work?, do you really want to highlight column D where previously you said you wanted to highlight E:K but now you don't?

Mitchelson
05-22-2008, 02:10 PM
I'm still get an error message with the following code.

If Selection.Value > 0 Then

Please find attached the full workbook that I'm using. Its just show what I'm trying to achieve.


I posted before to get the mandatory fields.
h (http://vbaexpress.com/forum/showthread.php?t=19605)ttp://vbaexpress.com/forum/showthread.php?t=19605 (http://vbaexpress.com/forum/showthread.php?t=19605)

All that I was looking for was if the code found any blank cells within the mandatory fields for them to be highlighted and once they were filled in for the colour to be removed.

I was looking for only part of the code so I could try do the rest on my own but I still don't fully understand VBA.

Simon Lloyd
05-23-2008, 01:01 AM
????? Well did it work?, do you really want to highlight column D where previously you said you wanted to highlight E:K but now you don't?still without answering any of my questions you posted,

I'm still get an error message with the following code.
VBA:


If Selection.Value > 0 Then

Thats your line not mine!, it states that if the value of the selection is greater than 0......! you are working with multiple cells so cant use this.
I was looking for only part of the code so I could try do the rest on my own I have given you the tools to experiment with, look up referencing multiple ranges.

Sorry but i'm done with this thread now!

Mitchelson
05-23-2008, 05:18 AM
????? Well did it work?, do you really want to highlight column D where previously you said you wanted to highlight E:K but now you don't?Yesterday 07:38 PM

Simon, your code (see below) that you post does work.

Sub Select_Blanks()
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("D5:D" & Range("D" & Rows.Count).End(xlUp).Row) _
.SpecialCells(xlCellTypeConstants)
If Range(MyCell.Offset(0, 1).Address & ":" & MyCell.Offset(0, 7).Address) _
.SpecialCells(xlCellTypeBlanks).Count > 0 Then
MsgBox "missing data"
Range(MyCell.Offset(0, 1).Address & ":" & MyCell.Offset(0, 7).Address) _
.SpecialCells(xlCellTypeBlanks).Select
End If
Next MyCell
End Sub

I will mark this thread as solved.

You are right, you did answer my original question.

Thank you for your time.

Mitchelson
05-23-2008, 11:49 AM
Thank you Simon Lloyd for forcing me to getting the code to work on my own. :beerchug:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String

With Worksheets("Tickets")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To LastRow

tmp = ""

If .Cells(i, "D").Value = "Timesheet" Then
If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K") _
.Resize(, 1)) < 3 Then
tmp = "Completed all highlighted cells before closing workbook" & vbNewLine
End If
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("D5:D" & Range("D" & Rows.Count).End(xlUp).Row) _
.SpecialCells(xlCellTypeConstants)
Range((MyCell.Offset(0, 1).Address) & "," & (MyCell.Offset(0, 5).Address) _
& "," & (MyCell.Offset(0, 7).Address)).Select

With slection
Dim cell As Range
For Each cell In Selection
If cell.Value > 0 Then
cell.Interior.ColorIndex = xlNone
ElseIf cell.Value = "" Then
cell.Interior.ColorIndex = 6
End If
Next cell
End With
Next MyCell
End If

If tmp <> "" Then
msg = msg & "Row " & i & " value = " & .Cells(i, "D").Value & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With
End Sub



This is what I wanted the code to do.

1) If cell in any row and column "D" = "Timesheet" then stop the user from closing the workbook and give a error message if it found that cells in column "E", "I" & "J" had no data.
2) To highlight the cells that had that had the missing data.
3) To remove the colour in the cell then data was filled in.
4) For workbook to be saved and closed once all missing data was completed

The above code meet that criteria :clap:

Once again, thank you xld and Simon Lloyd for your time spend helping me with this piece of work.

Simon Lloyd
05-23-2008, 11:53 AM
It's not a case of forcing you, when you pick up things for yourself its a great rush and of course what you have learnt sticks!

However do recheck your code by stepping through it step by step as it still doesn't do what your previous posts have asked especially as you still have that typo i pointed out!

Mitchelson
05-29-2008, 02:19 AM
Hi Simon,

I've finally had a chance to re-check the VBA code in question as you
suggested. I think the following code below meets my requirements.

Only thing that I've moved out of VBA is when the cell value is greater than 0 to remove the colour. I'm just using conditional formatting to achieve this as the Sub only activates when the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String
Dim cell As Range

With Worksheets("Tickets")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To LastRow

tmp = ""

If .Cells(i, "D").Value = "Timesheet" Then

If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K") _
.Resize(, 1)) < 3 Then
tmp = "Completed all highlighted cells before closing workbook" & vbNewLine
Range(("E" & i) & "," & ("I" & i) & "," & ("K" & i)).Select
For Each cell In Selection
If cell.Value = "" Then
cell.Interior.ColorIndex = 6
End If
Next cell
End If
End If

If tmp <> "" Then
msg = msg & "Row " & i & " value = " & .Cells(i, "D").Value & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With

End Sub


Do you have any more tips to help make the code work better?

Kind Regards