Conditional Formatting across Sheets

[Corey]

I have 2 sheets i need to compare values with each other.
If there is a MATCH in value then i need the cell value to be changed to
Strikethrough.

There is 3 IF conditions though.

something like this:

=IF(AND(MATCH(SHEET8.D.VALUE =
SHEET4.A:A.VALUE),(MATCH(SHEET8.D.OFFSET(1,0).VALUE =
SHEET4.A:A.OFFSET(2,2).VALUE),(MATCHSHEET8.D.OFFSET(2,-3).VALUE =
SHEET4.A:A.OFFSET(2,-1).VALUE),FONT.STRIKETHROUGH=TRUE,FONT.STRIKETHROUGH=FALSE)

Can/How would i go about that ?
Each D and A:A needs to be a cell value that matches another cell value
from across sheets.

Possible?

Corey....

 

[Bob Phillips]

In CF, you just need to return a True/False and you use the Format to set
the style. So your formula would be

=AND(condition1, condition2, condition3)

and then format for strikethrough.

One other issue is that with CF you cannot refer directly to other sheets,
so you need to create an Excel defined name (Insert>Name>Define Name) for
any ranges on other sheets.

Your formula is syntactically incorrect, you MATCH a single value against a
list, not a list against a list. What is the logical requirement?



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Corey]

Bob,
The logical requirement is to MATCH a Value in a cell in sheet4 Column A
with a value in Sheet8 Column D, with the condition that other values MATCH
other values that are NEAR(Offset) the value.

In the following:
If in sheet8 D VALUE(X)= sheet4 A:A VALUE & THE VALUE IN sheet8
D.offset(-3,2) =sheet4 C:C .offset(2,-1) & sheet8 D.offset(0,1)=sheet4
..offset.range(2,2)22,2)
eg.
Sheet8
A B C D E F H I J K L M N
1 Y
2
3
4 X
5 Z
6
7
8
9
10


Sheet4
A B C D E F H I J K L M N
1
2
3
4
5 Y
6 X
7
8 Z
9
10



Result to have a MATCHING value(Z) in sheet4 Column C font changed to
Strikethrough.


Corey....

 

[Bob Phillips]

Still struggling with the formula. Your new details do not conform to what
you originally gave.

Give an example using actual cell references, not Offsets, it will be
simpler. Something like

If A2 found in Column D of Sheet 8 And
If C3 is found in the row below in Sheet 8 column D and ...


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Corey]

Ok,
sheet4 Column A are ID Numbers every 25 rows(rest ="")
Sheet4 Column C has Names AND numerical values
sheet8 Column's D,F,H,J&L has ID Numbers AND numerical values
sheet8 Column F has a name values


if D4 found in Column D of Sheet8 AND A2 found in Column A of Sheet4 values
MATCH
AND
if F1 found in Column F in Sheet8 AND C1 found in Column C of Sheet4 values
MATCH
AND
if D5 found in Colum D of Sheet8 AND C8 found in Column C of Sheet4 values
MATCH
then C8 found in Column C of Sheet4 is font.Strikethrough = True.


ALL values are in the SAME Columns, except for the 1st IF where i have
sheet8 Columns D,F,H,J&L with a value from sheet4



Corey....

 

[Corey]

Not sure if this assists, but what ever selection from this combobox code is
chosen, i need that value in sheet(InspectionData) to change to
Strikethrough.

Thought it may be easier via CD than Code??


Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
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) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = ComboBox28.Text And
..Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And .Cells(myrow,
1).Offset(0, 0).Value = ComboBox5.Text And IsNumeric(Trim(Mid(.Cells(myrow,
1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough =
False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

CHOSEN Value to Change sheet(InspectionData) value to Strikethrough font, so
it will NOT list AGAIN.

Corey....