Hello Peter,
I suspect the usual problem is trying to describe what I want to do in writing and the reader trying to interpret my comments. Screen shots probably should be used more often as it is the usual story of "a picture tells a thousand words".
The way the worksheet is used is like this...
- I enter the plan number of a structure in column B (PNxxx)
- Below the plan number the photo details taken of that particular structure are listed.
- In column C I enter the associated photo number against the photo details in column B.
- After a blank row/s the next plan number and photo details are entered and so on.
- This is done for each structure inspected during that batch of inspections.
- At a later date an engineer will then work out the order of the photos by numbering them in column A.
- I can then convert the data from columns A & B into a CSV file and import the CSV data into another program against each particular structures number.
So back to what I was trying to achieve...
There have been times when the engineer gets lost when numbering the order of the photos in a small contiguous range and doubles up.
So what I was trying to do was have the duplicate numbers highlighted in red whenever they make a mistake within each individual contiguous range.
In the exaggerated screen shot the first entry is the plan number, PN1038, in B3 and the photo data in B4:C7. A duplicate has been entered in A7 and it would be highlighted in red until it is corrected.
The next range of data for PN2430 is yet to be completed, but already A14 has been flagged as a duplicate.
| Mr Excel Posting_2.xls |
|---|
|
|---|
| A | B | C | D |
|---|
| 1 | ORDER | DESCRIPTION(BRIDGENUMBERMUSTSTARTWITHPN) | PHOTOID | |
|---|
| 2 | | | | |
|---|
| 3 | | PN1038 | | |
|---|
| 4 | 1 | GENERALUNDERSIDE | 8800 | |
|---|
| 5 | 2 | NORTHERNELEVATION | 8798 | |
|---|
| 6 | 3 | BOLTSMISSING-NORTHERNSIDE | 8801 | |
|---|
| 7 | 1 | SOUTHERNELEVATION | 8799 | |
|---|
| 8 | | | | |
|---|
| 9 | | | | |
|---|
| 10 | | PN2430 | | |
|---|
| 11 | 1 | GENERALUNDERSIDE | 8803 | |
|---|
| 12 | 4 | DOWNSTREMELEVATION | 8806 | |
|---|
| 13 | 2 | D/SELEVATION | 8805 | |
|---|
| 14 | 4 | SOUTHERNAPPROACH | 8809 | |
|---|
| 15 | | NORTHERNAPPROACH | 8810 | |
|---|
| 16 | | | | |
|---|
| 17 | | | | |
|---|
| 18 | | PN5342 | | |
|---|
| 19 | | VICTORIANBORDERAPPROACH | 8810 | |
|---|
| 20 | | KEITHAPPROACH | 8811 | |
|---|
| 21 | | GENERALUNDERSIDE | 8812 | |
|---|
|
|---|
I have used the following conditional formatting:
Condition 1:
Formula is
=$C3<>""
When the photo number is entered into a cell in column C there are borders applied from A to C for that row.
Condition 2:
Formula is
=LEFT($B3,2)="PN"
When the PN is entered before structure number that cell data is shown as bold.
I have noted that the macros suggested kill the existing conditional formatting i.e. borders, etc.
Another solution to my problem could be just using another conditional formatting condition that would highlight duplicates when I run the sorting macro that Peter helped me out with on another post.
Formula is
=AND($A3<>"",$A3=$A2)
When there is a duplicate in column A either the individual cell or cells A:C would be highlighted red.
Obviously this extra condition would only show duplicates when the data is sorted not as they are entered (I also needed to make this condition 1 to work).
Code:
Option Explicit
Sub Sort_A_to_C()
With ActiveCell.CurrentRegion
If .Column<= 4 And .Rows.Count > 1 Then
.Resize(, 3).Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End If
End With
End Sub
For some work related reason the photo description text in column B must be uppercase (Smitty helped me out with this in a previous post).
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Target.Parent.Range("B:C")
If Intersect(myRng, Target.Cells) Is Nothing Then Exit Sub
Select Case Target.Column
Case 2 ' Column B
On Error Resume Next
Target.Value = UCase(Target.Value)
On Error Resume Next
Err.Clear
Case 3 ' Column C
Me.Parent.Save
End Select
End Sub
I added another case to Smitty's code but it combined data ranges:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Target.Parent.Range("A:C")
If Intersect(myRng, Target.Cells) Is Nothing Then Exit Sub
Select Case Target.Column
Case 1 ' Column A
On Error Resume Next
Call Sort_A_to_C
Err.Clear
Case 2 ' Column B
On Error Resume Next
Target.Value = UCase(Target.Value)
On Error Resume Next
Err.Clear
Case 3 ' Column C
Me.Parent.Save
End Select
End Sub
By calling the sort macro as numbers were entered in column A it meant the conditional formatting worked as data was entered.
However, I am starting to suspect that I should just keep it simple and use the extra conditional formatting condition when manually running the sort macro as it requires no code and does not remove existing borders.
Thanks again for all your help it is very much appreciated.
Regards,
Dave T
