Suppose you have a list of equipment as shown below and you would like to like c

Question

Suppose you have a list of equipment as shown below and you would like to like classify them. Write a VBA code that loops through the list once and color the rows with microscope red and spectrometer green.

Class                                       Name                        Quality
1 Microscope                         SEM                                2
2 Microscope                         TEM                                1
3 Spectrometer                      FIB                                  2
4 Spectrometer                      Rama                               3
5 Spectrometer                      XBS                                 2
6 Microscope                        Optical                               3
7 Spectrometer                     XANES                              2
8 Spectrometer                    EXAFS                                2
9 Spectrometer                     XRR                                   1

Explanation / Answer

first logic

sub test()

Dim rng as range, cell as range

Set rng=Range("A1:C10")

for each cell in rng

if(cell.value="Microscope") then

cell.font.color=vbred

else

cell.font.color=vbgreen

end if

next cell

end sub

Second logic

Sub test()
Dim i As Long
Dim j As Long

erow = Sheet5.Range("A1").End(xlDown).Row
lcolumn = Sheet5.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To erow
For j = 1 To lcolumn
If (Sheet5.Cells(i + 1, 1) = "Microscope") Then
Sheet5.Cells(i + 1, j).Font.Color = vbRed
Else
Sheet5.Cells(i + 1, j).Font.Color = vbGreen
End If
Next j
Next i
End Sub

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.