Problem with Chnaging fonts to Strikethrough

C

Corey

Basically i have 2 sheets
Sheet 1 has data in column A(Say called RL-Values) and related values in the
same row in Column L and every 9 Columns across through to Column GJ. (Say
Called LL-Values)

Sheet 2 may have a value from Sheet 1 Column A in Columns K and every 16
Columns across on the same row(K-BW)[5 cells in total]. (Say Called
URL-Values)
But each time there is a value in the column K,AA,AQ,BG or/and BW a value
also is found in the column to the Right(eg. If K=951 then L may be 50) and
so on across through each of the 5 possible cells(K-BW) that may contain a
value from Sheet1.(Say Called ULL-Values)

So recapping:
Sheet2 has values in Columns K,AA,AQ,BG,BW that are found in Column A in
Sheet1.

If a value IS in a Column in Sheet2, then the adjacent Column(L for Column K
value etc) will have a value from Sheet1 Column L,U,AD,AM,AV....[every 9
Columns] until GJ.

I am trying to make a macro to search the RL-Values for a match in the
URL-Values, then find the ralated value in the ULL-Values in the LL-Values,
and have the font changed to Strikethough.

It is easy in my mind, but difficult to explain in words as to the required
outcome.

Previously i used the below code to do this, but i have now modified the way
the data is stored, and it is now stored in a single row instead in about 22
rows.

****************************
Sub RemoveUsedRolls()
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
LastCell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" And .Cells(myrow, 1).Font.Bold = True
Then
For i = 2 To 22
If .Cells(myrow, 1).Offset(i, 2).Value <> "" And .Cells(myrow,
1).Offset(i, 2).Font.Strikethrough = True _
And .Cells(myrow, 1).Offset(i, 2).Font.Strikethrough <> False
Then
.Cells(myrow, 1).Font.Strikethrough = True
End If
Next i
End If
Next
End With

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" And .Cells(myrow, 1).Font.Bold = True
Then
For i = 2 To 22
If .Cells(myrow, 1).Offset(i, 2).Value <> "" And .Cells(myrow,
1).Offset(i, 2).Font.Strikethrough = False Then
.Cells(myrow, 1).Font.Strikethrough = False
End If
Next i
End If
Next
End With
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub
****************************

Corey...
 
C

Corey

I think in logical terms the procedure required is a s follows:

If Sheet2.column(K).value <>"" then find Sheet2.Column(K).value in
Sheet1.Column(A)
If Sheet1.Column(A).Value=Sheet2.Column(K).Value then Find
Sheet2.Column(K).Offset(0,1).value in same row as
Sheet1.Column(A).value,found in Columns(L) and Step 9 Across the row until
Colum GJ
Then When found, change Font to Strikethrough.

Also

If ALL values from Sheet1.Column(L) - Step 9 are Strikethrough, them also
make Sheet1.Column(A) value Strike through too.


Corey....

Corey said:
Basically i have 2 sheets
Sheet 1 has data in column A(Say called RL-Values) and related values in
the same row in Column L and every 9 Columns across through to Column GJ.
(Say Called LL-Values)

Sheet 2 may have a value from Sheet 1 Column A in Columns K and every 16
Columns across on the same row(K-BW)[5 cells in total]. (Say Called
URL-Values)
But each time there is a value in the column K,AA,AQ,BG or/and BW a value
also is found in the column to the Right(eg. If K=951 then L may be 50)
and so on across through each of the 5 possible cells(K-BW) that may
contain a value from Sheet1.(Say Called ULL-Values)

So recapping:
Sheet2 has values in Columns K,AA,AQ,BG,BW that are found in Column A in
Sheet1.

If a value IS in a Column in Sheet2, then the adjacent Column(L for Column
K value etc) will have a value from Sheet1 Column L,U,AD,AM,AV....[every 9
Columns] until GJ.

I am trying to make a macro to search the RL-Values for a match in the
URL-Values, then find the ralated value in the ULL-Values in the
LL-Values, and have the font changed to Strikethough.

It is easy in my mind, but difficult to explain in words as to the
required outcome.

Previously i used the below code to do this, but i have now modified the
way the data is stored, and it is now stored in a single row instead in
about 22 rows.

****************************
Sub RemoveUsedRolls()
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
LastCell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" And .Cells(myrow, 1).Font.Bold = True
Then
For i = 2 To 22
If .Cells(myrow, 1).Offset(i, 2).Value <> "" And .Cells(myrow,
1).Offset(i, 2).Font.Strikethrough = True _
And .Cells(myrow, 1).Offset(i, 2).Font.Strikethrough <> False
Then
.Cells(myrow, 1).Font.Strikethrough = True
End If
Next i
End If
Next
End With

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" And .Cells(myrow, 1).Font.Bold = True
Then
For i = 2 To 22
If .Cells(myrow, 1).Offset(i, 2).Value <> "" And .Cells(myrow,
1).Offset(i, 2).Font.Strikethrough = False Then
.Cells(myrow, 1).Font.Strikethrough = False
End If
Next i
End If
Next
End With
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub
****************************

Corey...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to change For i - to refer to specific columns instead of rows 6
Help with code 8
Why does the line of code NOT work? 2
Gaining the Column A Value 8
How to code, AND where to place it !! 6
>= Greater than or Equal to Not working 2
Do i need a Next or Loop statement ? 1
Remove Numerical Values from Listbox and Certain Text Value 3

Top