Excel Help ~

GillesD or anybody who's good at excel,

I would like to compare data like this:

1) In the range M2:BI2, find if there's any cell that has the same data value as D2. If so, make that cell BOLD, ITALIC, and make it RED WORD, BLUE BACKGROUND

2) Same thing with 1), in the range M2:BI2, find there's any cell that has the same data value as E2 (not D2), then make it BOLD, ITALIC, and make it RED WORD, BLUE BACKGROUND

3) same thing with 1), but compare the range M2:BI2 with F2 this time..


When 1) ~ 3) are done, going to next row automatically, so the range would be M3:BI3, and compare w/ D3.. then E3, then F3...

When done again, going to next row, M4:BI4, compare w/ D4, E4, F4, make the same cell format if the condtion is met....

I have to compare it up to M192:BI192, (D192, E192, F192) for this worksheet...
for the other worksheet, it's up to row300... so it would be great if i can choose "compare up to row number #?"....

is there any quick efficent way of doing it other than what i'm doing now? (Find/Replace)

thanks in advance

Dannys K.
 

GillesD

Member
Highlighting specific cells

Yes I think it is feasible but not through formulas.

I will try to look for a small VBA program that would do the job.
 

GillesD

Member
Highlighting specific cells

The VBA program listed below will highlight cells meeting the conditions listed in the first post of this thread.

- It assumes all values are integers.
- The cursor must be on the first cell for evaluation when starting the program (cell D2 in this case).
- The program will end when it encounters a blank cell (in column D).

To set it up, open a module in your Excel file and copy the listed program into the module. Place the cursor on the first cell you want evaluated in column D and start the program MarkCells with the Tools Macro command.

The program is:

Option Explicit

Dim nVal0 As Integer, nVal1 As Integer
Dim nVal2 As Integer, nVal3 As Integer
Dim I As Integer

Sub MarkCells()
Application.ScreenUpdating = False
Do While ActiveCell.Value <> ""
nVal1 = ActiveCell.Offset(0, 0).Value
nVal2 = ActiveCell.Offset(0, 1).Value
nVal3 = ActiveCell.Offset(0, 2).Value
For I = 9 To 57
nVal0 = ActiveCell.Offset(0, I).Value
If nVal0 = nVal1 Or nVal0 = nVal2 Or nVal0 = nVal3 Then
With ActiveCell.Offset(0, I)
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 9
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
End With
End If
Next I
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
 
Re: Highlighting specific cells

GillesD said:
The VBA program listed below will highlight cells meeting the conditions listed in the first post of this thread.

- It assumes all values are integers.
- The cursor must be on the first cell for evaluation when starting the program (cell D2 in this case).
- The program will end when it encounters a blank cell (in column D).

To set it up, open a module in your Excel file and copy the listed program into the module. Place the cursor on the first cell you want evaluated in column D and start the program MarkCells with the Tools Macro command.

The program is:

Option Explicit

Dim nVal0 As Integer, nVal1 As Integer
Dim nVal2 As Integer, nVal3 As Integer
Dim I As Integer

Sub MarkCells()
Application.ScreenUpdating = False
Do While ActiveCell.Value <> ""
nVal1 = ActiveCell.Offset(0, 0).Value
nVal2 = ActiveCell.Offset(0, 1).Value
nVal3 = ActiveCell.Offset(0, 2).Value
For I = 9 To 57
nVal0 = ActiveCell.Offset(0, I).Value
If nVal0 = nVal1 Or nVal0 = nVal2 Or nVal0 = nVal3 Then
With ActiveCell.Offset(0, I)
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 9
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
End With
End If
Next I
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub

GillesD,

it works perfectly, wow...
i can't tell you how thankful i'm for your help on this, it saves me so much time:)
Your excel knowledge is amazing... I had friends told me that I could do use the "Conditional Format" thing, and then just keep copying the "format" for every cell... this approach is faster than my original way of doing it..

but nothing's faster than your macro...really thankful for it,
thanx:agree2: :)
 

Sidebar

Top