A Conditional Formatting Problem

Published Date
01 - Feb - 2006
| Last Updated
01 - Feb - 2006
A Conditional Formatting Problem
The data being imported in a particular column can either be text (such as "17 U") or numeric (such as "32"). The conditional format checks to see if the value in the cell is greater than zero, in which case the value is underlined. This won't work properly with the imported data because not only does Excel treat the text ("17 U") as text, but it also treats the numeric ("32") as text. This makes sense, since Excel treats the entire column as text rather than changing data format for each cell in the column.

There are a couple of ways you can fix this problem. One is to change the formula you are using in your conditional format. Instead of checking to see if the value is greater than zero, use the following formula (set the conditional check to "Format Is"):

=VALUE(E3) > 0
This formula uses the VALUE function to check what is in cell E3. If the contents are a number-even if it is formatted as text by Excel-then the formula returns True, and the condition is met for the formatting. If the contents of E3 really are text (as in "17 U"), then the formula returns a #VALUE error, which does not satisfy the condition, and the formatting is not applied.

Another approach is to force Excel to evaluate the imported cells and convert them to numeric values, if appropriate. An easy way to do this is as follows: after importing the data, select a blank cell from a column outside the range of those you just imported. Press [Ctrl] [C]. Select the cells that you want evaluated by Excel. For instance, select the column that contains the text values and the numeric values formatted as text. Choose Paste Special from the Edit menu. Click the 'Add' radio button and click 'OK'.

What you just did was to "add" the contents of the Clipboard to all the cells you selected. If the cells contained real text, then nothing happened to those cells; they remain the same and are still treated as text. If the cells contained a numeric value, then Excel treats it as a number and adds zero to it. This value, as a numeric, is deposited back in the cell, and treated as a real number. This means that the conditional formatting test that you previously set up should work just fine on those cells since they are no longer treated as text.

Copying Conditional Formatting
In Excel, conditional formatting is considered part of the regular formatting of a cell. If you want to copy conditional formatting from one cell to another, you can do so by simply copying the cell and pasting it (or its format) to another cell. If you want to copy a conditional format to a range of cells (and only the conditional format), the easiest way to do so is this: select the range to which you want the conditional format copied. Make sure, however, that the cell whose conditional format you want copied is part of the range. Choose 'Conditional Format' from the 'Format' menu. You will see the 'Conditional Format' dialog box. The format should already be filled in. Click 'OK'. Excel does the rest and copies the conditional formatting, as you desired.

What Are Add-Ins?
Many features of Excel are available only through what are called add-ins, for instance, the Analysis ToolPak. The tools available in add-ins are not part of the basic Excel system, but can be added to the system as and when needed. Add-ins are programs that have been "added to" Excel in such a way that they appear to be part of Excel itself.

You can find many useful add-ins for Excel on the Net.

Excel allows you to translate your macro programs into add-ins. Converting them to add-ins has several advantages:

1. The program code cannot be altered by others.
2. The program code runs a bit quicker.
3. The add-in is available without needing to open any particular workbook.
4. The functions provided by the add-in appear to be a part of Excel.
Add-ins are, therefore, a special type of workbook that you've converted to a format understood by Excel.

Creating An Add-In
Any Excel workbook can be converted to an add-in. To create a protected add-in file, first load the workbook that will become your add-in. Start the Visual Basic Editor by choosing Macro from the Tools menu, then choosing Visual Basic Editor. At the very top of the Project window, select the bold entry that declares the name of the VBA project that is open. Choose the Properties option from the Tools menu. This displays the Project Properties dialog box. Make sure the Protection tab is selected, and make sure the 'Lock Project For Viewing' checkbox is selected. Enter a password in both fields at the bottom of the dialog box. Click 'OK'-the dialog box closes.

Close the Visual Basic Editor and return to the Excel workbook. Choose File > Properties. In the Summary tab, make sure the 'Title' field is filled in. What you enter here will appear in the 'Add-Ins' dialog box used by Excel. Make sure the 'Comments' field is filled in. What you enter here will appear in the description area of the 'Add-Ins' dialog box used by Excel. Click 'OK' to close the dialog box. Now choose 'Save As'. Using the 'Save As Type' pull-down list, specify the Microsoft Excel Add-In (*.xla) file type. Specify a name in the 'File Name' field. Click 'Save'. Your add-in file has been created. Finally, close the workbook you just saved as an add-in.
Using An Add-In
After you have created your own add-in, you can use it in your system. Once the add-in has been loaded, the functions or features in the add-in become available to any other workbook you may have open, or any time you are using Excel. To use your add-in, choose 'Add-Ins' from the 'Tools' menu. This displays the 'Add-Ins' dialog box.

Save a workbook as an add-in by selecting "Add-ins" under save As

After you've saved your add-in,it will show up under "Add-Ins"

If your custom add-in is visible in the dialog box, click the checkbox beside it and click 'OK'-you're done. Otherwise, click 'Browse', and use the controls in the dialog box to locate and select your custom add-in.

Automatically Loading Add-ins
First load the worksheet for which you want a specific add-in loaded. Then press [Alt] [F11] to display the VBA Editor. Double-click on the 'This Workbook' object in the Project Explorer. Excel opens a code window for This Workbook. Place the following macros in the code window:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Add-In Name").Installed = False
End Sub
Private Sub Workbook_Open()
AddIns("Add-In Name").Installed = True
End Sub
(In the code, change "Add-In Name" to the name of the add-in.)
Close the VBA Editor and save your worksheet. If you're not sure of the correct name for a particular add-in, you can use the macro recorder to record the process of activating an add-in. That
will show you the exact
name you should use in the above macros.

Using CF To Draw Attention
You can use the conditional formatting feature to help draw attention to cells that contain specific text in which you are interested. For instance, if you have a range of cells and you want to know which ones contain the letters "DIGIT", then you can do the following: first select the range of cells. Choose 'Conditional Formatting' from the Format menu. In the drop-down 'Condition' list, choose "Formula Is". In the formula box, enter the following formula. (Replace 'A1' with the address of the cell in the upper-left corner of the range you selected.)

Choose a range of cells and apply conditional formatting by choosing "Formula Is"

Once you've applied your criteria, your specified word(s) will appear in your chosen format

Click 'Format'. Excel displays the 'Format Cells' dialog box. Using the controls in the dialog box, specify a format that you want used for those cells that contain the specified text. For instance, you may want bold text in a yellow typeface. Click 'OK' twice.

You can make this approach even more general-purpose by specifying a cell that contains what you want to search for. For instance, if you type "DIGIT" in cell F7, then you could replace the formula with the following:

Now, you can search for something different just by changing the characters in cell F7.

External Data Validation
Data validation allows you to control what information is entered into a worksheet. Validating based on information internal to a workbook is easy; validating to information in another workbook can be a real pain. Normally, Excel expects you to specify your validation range as being on the same worksheet where you are defining the validation rule. If you try to enter a range that is on another worksheet or in another workbook, Excel brings up an error message.

What if you want the validation range to be on another worksheet, just so you don't clutter up the current worksheet with extraneous data? The easiest way to do that is to follow these general steps: first, select the range of cells you want to use as your data validation values. Choose 'Name' from the 'Insert' menu, and then 'Define' from the submenu. Excel displays the 'Define Name' dialog box. Enter a unique name for your data validation range, such as "MyValRange", then click 'OK'. Now select the cell for which you want to define a validation rule. Choose 'Validation' from the Data menu. Excel displays the 'Data Validation' dialog box.

Use the 'Allow' and 'Data' drop-down lists to specify how you want Excel to validate the data. Depending on your selections, you will see a Value, Minimum, or Maximum boxes appear in the dialog box. In the appropriate boxes (Value, Minimum, or Maximum), specify the name you gave your data validation range ("MyValRange".) Click 'OK'.

This approach works fine if the data validation range is in the same workbook. What if you want to use a data validation range that is in an entirely different workbook? You can trick Excel into accepting your external reference if, instead of specifying a name, you enter a formula such as the following:

This formula uses the INDIRECT function to return the value at a cell on another worksheet, and the data validation feature will accept it with no problems. In this case the cell being checked is at cell D6 on Sheet1 of Book2. In order for this to work, you will need to make sure that Book2 is open at the same time that your main workbook is open.
Advanced Filtering
There are some situations in which AutoFilter just can't cope up. For instance, you might need to perform a calculation in a filter comparison. In these cases, you can use the advanced filtering capabilities of Excel.
Advanced filtering requires that you set up a criteria range in your worksheet. This criteria range is not part of your data list, but instead is used to signify how you want filtering to be performed. Typically, you would place your criteria before your data list, but you can also place it after. The important thing is that you separate your criteria from you data list by at least one empty row. Otherwise, Excel may think that the criteria are part of the actual data list. The criteria are entered in your worksheet such that each column represents a different logical AND comparison, and each row represents a different logical OR comparison. An example is in order here.

Let's say you have a data list that starts in the sixth row of a worksheet. You have set aside the rows above this to specify your filtering criteria. The data list contains columns that describe information in your inventory. There are columns for item numbers, description, location, quantity, value, and the like. There is also a calculated column that indicates the loss derived from each inventory item.

At some time you may want to filter your data list so it shows only a limited subset of your inventory items. For instance, you might want to see only those items for which the quantity is over 2,500 and loss is less than 1,000, or those items where the quantity is greater than 7,500, or those items where loss is under 100. This is much more complex than you can perform using a custom AutoFilter.

To set up such a filter, all you need to do is set your criteria. In this case, you would use cells A1:B4 as follows:

            A             B
1    Quantity     Loss
    >2500     <1000
3     >7500

The first row shows the field names to be used in comparisons, while the second through fourth rows define the actual comparisons. Notice that because there are two tests in the second row, these are considered an AND condition, and those on the other rows are considered OR conditions.

To apply these filtering criteria, follow these steps: first select any cell in the data list (not in the criteria). Choose Data > Filter. Choose the 'Advanced Filter' option from the submenu. Excel displays the 'Advanced Filter' dialog box. The 'List Range' should automatically be set to your data list. Select the 'Criteria Range' field. The insertion point should appear within the field. Use the mouse to select the criteria range, cells A1:B4. Click 'OK'.

Ensuring Unique Values In A Column

To ensure unique values, you can use Data Validation for range

With Data Validation, typing in an incorrect value brings up an error

When you are entering information in a worksheet, you may want to ensure that the data being entered is actually unique for a particular column. For instance, if you are entering a series of invoice numbers in column D of a worksheet, you may want to ensure that you don't enter the same invoice number twice.

There are a couple of ways you can approach such a problem. If you are always entering your new information at the bottom of a column, you can use Excel's data validation feature to make sure that whatever you are entering in the current cell does not match anything higher up in the column. Simply follow these steps: first, select column D. Choose 'Validation' from the 'Data' menu. Excel displays the 'Data Validation' dialog box. Using the 'Allow' drop-down list, choose the 'Custom' option. In the 'Formula' box, enter the following formula:

Click 'OK'. If you may be entering data anyplace within column D, and you want to know if you are entering a value that is elsewhere in the column (above or below the current cell), then you can follow the above steps, but use this formula instead:

Dynamic Hyperlinks
You can create dynamic hyperlinks in Excel that act like HTML forms without having to know much about HTML. These hyperlinks can come in handy when using Excel as an interface to the Internet. As an example, let's create a Google search form. First, do a Google search for the keyword "Excel." Then, take a look at the URL in your browser's address bar. It may look something like this:

The parameters following the question mark (?) are the name-value pairs submitted by the HTML form. But, you don't need to know a whole lot about that. In this case, the main thing to consider is the search term, where "q" is the name of the parameter, and "Excel" is the value.
Using the HYPERLINK function along with the CONCATENATE function (or just the ampersand), you can easily assemble a link and create a dynamic form using cells as fields: in cell B3, enter the keyword "Excel Web Queries." And in cell B4, enter the following formula:
=HYPERLINK("https://www.google.com/search?q="&B3&"&safe=active","Search Google")

Now you have your own simple HTML form inside Excel. You can create much longer hyperlinks and include multiple cell references, but there is a limit in Excel to how long the hyperlink can be (about 248 characters). To use the form, just enter your keywords into cell B3, and click on the hyperlink in cell B4.
Deleting Many Hyperlinks
If you inherit worksheets from other people, you may find that some worksheets contain many, many hyperlinks. These hyperlinks are often automatically created by Excel as you import or enter information in the worksheet. If you want to delete these hyperlinks, you can do so by right-clicking on them and choosing Hyperlink > Remove Hyperlink from the Context menu. Doing this with dozens or hundreds of hyperlinks can consume a huge amount of time. To delete all the hyperlinks in the active worksheet at the same time, you can use a one-line macro:
Sub DeleteHyper()
End Sub
Select the worksheet
you want to affect, and run the macro!

Recalculate Formulas
There are two ways Excel recalculates formulas-automatic and manual. Go to Tools > Options, and click the Calculations tab. Select 'Automatic Update' if you wish to have Excel always update your data. Select 'Manual Update' if you wish to have Excel update formulas only when you tell it to. Under Manual Update:
[F9]: Calculates all worksheets in all open workbooks. When a portion of a formula is selected, it calculates the selected portion. You can then press [Enter] (or [Ctrl] [Shift] [Enter] for array formulas): Replaces the selected portion with the calculated value.
[Shift] [F9]: Calculates the active worksheet.

[Ctrl] [Alt] [F9]: Calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

[Ctrl] [Alt] [Shift] [F9]: Rechecks dependent formulas and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

Opening An HTML Page In Excel
Excel is "Web aware," meaning that the program knows how to handle hyperlinks. You can add a hyperlink in a document, click on that link, and Excel opens your Web browser and displays the contents of that link in the browser. (You can also create a hyperlink to other Office documents, including Excel workbooks.) You can even create hyperlinks to different objects on your worksheet, such as a command button in a form.

What if you want to start the browser and open an HTML file from within a VBA macro? There are a couple of ways that you can do this. The first is to simply open a new Internet Explorer object within your code. A macro to do this would look like this:
Sub DoBrowse1()
Dim ie As Object
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = True
ie.Navigate "c:tempMyHTMLfile.htm"
End Sub
This macro will open the file "c:temp MyHTMLfile.htm" in a new Internet Explorer window. If you want to instead open a Web page from over the Internet, you can do so simply by changing where you want to navigate-replace the file path with a URL.

Another way to accomplish the same task is to rely on Excel to figure out what your default browser is and open the HTML resource. Use the following macro:
Sub DoBrowse2()
ActiveWorkbook.FollowHyperlink _
Address:="c:tempMyHTMLfile.htm", _
End Sub
Again, the browser opens a new window and displays the specified file. You can change the Address parameter to any URL
you want.

Data In A Single Column
Say you use a program to create a CSV file. This file can then be opened in Excel for further analysis. When you double-click the CSV file in Windows, Excel starts and loads the file. The problem is that the file, when opened, isn't parsed by Excel. Instead of the comma-separated values being in different columns, every record appears in a single column. The reason for this is quite simple, and has to do with the format in which the data is stored in the CSV file by the non-Excel program. To understand that, you need to understand how Excel opens CSV files.

When you open a CSV file in Excel, the program treats any commas in the file as delimiters. You cannot override this automatic filtering when opening the file. So, how does Excel treat the incoming data? Consider that the CSV file contains the following four records:
As far as Excel is concerned, the first record has five fields, separated by commas. The second record has only two fields, "a,b,c,d" and "e." In other words, Excel ignores any commas that may appear between the quote marks. Given the effect that inverted commas have, the third record has only four fields, and the fourth record is interpreted to have only a single field.

What this has to do with the CSV file loading incorrectly is that it is very possible that the program creating the CSV file is putting a pair of inverted commas around each record. This would cause everything in the record to be treated as a single field by Excel, which means it ends up in a single column when the CSV file is loaded.

There are a couple of ways to verify this. The first is to simply open the CSV file with Notepad and look at each record. Another way is to rename the CSV file so that its extension is not '.csv' but '.txt'. When you open this file within Excel, the Text Import Wizard starts. Choose 'Delimited', click 'Next', and then you can see what delimiters are chosen. Pay attention to the Text Qualifier; if you change it, you can immediately see at the bottom of the dialog box how Excel interprets the file's records.

If you find that there are extra inverted commas around each record in the CSV file, there are three things you can do.

The first is to change the program that creates the CSV file so that it doesn't add the extra inverted commas-you'll then be able to import with no problem.

The second is to go ahead and load the CSV file into Excel, such that each record is in column A. The surrounding inverted commas will be gone, stripped out by the import process. This means that you can now use Data > Text to Columns to separate the data in column A into individual columns.

The third thing you can do is to create a macro that will open the CSV file and parse it for you. This is particularly helpful if you will be opening, over time, many CSV files that have the exact same format. Your macro could be as elaborate as desired, even formatting columns and processing data as it is imported.

Some Linux distros such Fedora don't play back MP3 or AVI files. Users have to go through the ordeal of getting them to work on their machine. Here we will see how to install XMMS player to play MP3 files on a Fedora Core 3 box, permanently create aliases for long commands, use cdrecord to burn CDs, and installing an alternative for MSN messenger.

These tips can be very useful for those new to Linux. We will be using Fedora Core 3 for this purpose.

Installing MSN Messenger
You can use Gaim, a multi-messenger, to connect to MSN messenger, but if you want to have a dedicated messenger that looks and feels more like Windows MSN messenger, you're probably looking for AMSN. You can download the installer from https://amsn. sourceforge.net. You may choose to download ready made installers depending on the distribution. We will tell you how to install it from a tarball. After you have downloaded the tarball, switch to the directory where you have saved it and untar/ uncompress the files using the following command:

# tar -zxvf amsn-0.95.tar.gz

The list of commands that run when you're trying to install aMSn

aMSN  look just like Windows'MSN Messenger

Now we have to configure aMSN by running its configure script; but always read the README or the INSTALL file before you proceed as it gives information of dependencies if any. You should have tcl-dev and tk-dev packages. Now, run the configure script by typing in

#./configure [Enter]

This script will check for all the dependent packages and create a Makefile for final installation based on the detected system configuration. On successful completion of configure, run the #make command. This will take some time; after that, finalise the installation with the command #make install. You can launch the messenger immediately by typing "amsn" at the shell prompt. Or reboot to find the link on Hat > Internet > aMSN.

Your Linux alternative for MSN messenger is as shown in figure 3.
Using cdrecord
Most Linux distributions have an inbuilt command line CD burning tool called 'cdrecord'. Using this tool isn't as simple as it was in Redhat 9 shrike. A few things need to be configured before you can proceed with the steps to record a CD.

First, type in the command cdrecord -scanbus at the shell prompt; if the output shows your CD/DVD writer on 1,x,x or 2,x,x, then we have work on our hands. But if the output displays the drive on 0,x,x (ideally 0,0,0) then skip to the MKISOFS step. The steps involved:
1. Identifying your drive's device driver
2. Loading ide-scsi emulation

When identifying your drive's device driver, it is advisable to use the CD/DVD- Writer as secondary master, be it Windows or Linux, or any OS for that matter. In our example, the DVD-Writer is connected as secondary master, and the device driver then becomes /dev/hdc. To know this without flirting with the hardware, type in "dmesg |grep -i CD" at the shell prompt. The output will be similar to: "Added public key 6ECDA687281A73E5

Loading ide-scsi Emulation
IDE emulation is required for Fedora as the CD writers are loaded as scsi drive, and we need to emulate that to IDE manually (Users of Red Hat 9 or below do not need to do this). Just follow the following instructions:
Change to root user, and type in

/sbin/insmod -p ide-scsi and hit [Enter].

if you get an error then we have to make a few changes in a few important files. First create a file under /etc named "modules.conf" if it doesn't already exist, and add the line:

"alias scsi_hostadapter ide-scsi"

Save and close the file. Also, you can add the following command to /etc/rc.local so that it is executed at every boot:

"/sbin/insmod ide-scsi"

Now comes the critical part of the boot loader. Many people use grub, but some use lilo. Grub users should open the file /etc/grub.conf using the vi editor. Take the cursor to the line:
"kernel /vmlinuz-2.6.9-1.667 ro root=LABEL=/ rhgb quiet"
And add "hdc=ide-scsi" at the end.
Lilo users should open "lilo.conf" located under /etc using the vi editor, or any text editor of your preference, and add append="hdc=ide-scsi" to it. Note that hdc is the secondary master on the IDE cable that connects your CD/DVD drive to the motherboard of your system. Lastly, run lilo at the shell prompt to effect the changes.

Grub users need not run any command. Now reboot the system to initiate all changes. Login as root or switch to root using the "su" command and run

/sbin/insmod -p ide-scsi

to check if the settings have worked. You shouldn't get an error. Or try

#cdrecord -scanbus

The output should now show your CD/DVD drive on:
0,0,0     0) 'LITE-ON ' 'DVDRW SHM-165P6S' 'MS0C' Removable CD-ROM
You have successfully loaded ide-scsi emulation!

To burn a disc using cdrecord command, we have to create ISO filesystem containing all the files you want on your CD. To make an ISO file:

#mkisofs -v -o isofilename.iso file1 file2 file3... [Enter]

Or create a folder, say /tmp/Disc, and dump all the files you want to copy to a CD into this folder. Change the directory to Disc (here: /tmp/Disc) and then use the same command as:

#mkisofs -v -o isofilename.iso * [Enter]

Even better, use:

#mkisofs -v -o isofilename.iso -J -R /tm/Disc and hit [Enter]

-J is for Joliet extension (for Windows support) and -R is for RockRidge extesion. We now have a file (isofilename.iso) that contains all the files we want to write to a CD. Even downloaded ISO files or the one created in Windows will work. The last stage is to use the "cdrecord" command with the necessary switches.

You must know the speed of your drive; in our case it is 48x24x48, and also the speed that the CD-R is capable of handling. If the CD-R is 52x and the drive is capable of 40x, then you should burn at a max speed of 40x. And, if the drive is 52x and the CD-R is 32x, then the max speed you can record at is 32x when using "cdrecord". The command with best preferred switches, in our opinion, is:

# cdrecord dev=0,0,0 gracetime=2 fs=8m driveropts=burnfree,noforcespeed -v speed=24 -sao -eject /tmp/isofilename.iso

What the switches mean:
dev=x,x,x: the target drive.
gracetime: the time interval within which you can cancel the execution of burn command fs=#: the size on FIFO buffer. Number followed with 'm' gives size in MB driveropts= options: there are many options of which we feel burnfree and noforcespeed are useful burnfree: turns on buffer underrun protection noforcespeed: Some drives force a selected speed even if the media is poor, thus resulting in poor write quality. This options allows variable writing speed depending on the quality of the medium.

The list of commands that get executed when you're trying to install Xmms

-v: displays progress of disc writing
-sao: the same as -dao, i.e., Disc-At-Once
-eject: ejects the tray on completion or the writing process.

Playing MP3 On Linux
This is very easy provided you take the right download link. We searched in vain through most of the Linux software sites such as freshrpms.net; but all we could get is more dependencies.

To play MP3 on a Linux box, you need to install Xmms player which is a look alike of Winamp player. Download Xmms from softpedia.com: https://linux. softpedia.com/ get/Multimedia/Audio/XMMS-348.shtml, because it is error free and installs without a glitch. On completion of download, use the command:

#rpm -ivh xmms-1.2.10-1.i386.rpm and hit [Enter]

Xmms lists itself in the Start Menu under System Setting > Multimedia > XMMS. You can also use the command "xmms &" at the shell prompt to run it.

There isn't much difference in dealing with CD-RW except that you can erase the content of the media and the max writing speed doesn't exceed 24x. So, check if the CD-RW media contains any important data before you proceed to erase it. To erase the disc:

Winamp on Linux

# cdrecord dev=0,0,0 blank=fast and then hitting [Enter] is the fastest and most common usage. Alternate switches (blank=) are:
all: erases entire disc, takes along time track: erases a track session: erases the last session unclose: opens the last session In order to write data to the CD-RW, follow the instructions used in the CD-R, but take care to specify the correct writing speed. Check your Rewritable media for the maximum rewriting speed possible.

Creating Permanent Aliases For Commands
We use certain commands frequently; some are very long and irritating to type in again and again. You could use the 'alias' command to assign aliases, but the result is temporary. Let's be smarter, hack a script file, and set permanent aliases for such commands.

Creating a permanent alies

For our example, we'll take the command 'clear'. We use this command very frequently and many a time, it bugs one to re-type it! Let's set 'c' to perform the function of 'clear'. The temporary method is to use the command #alias c=clear and hit [Enter].

Change your directory to /etc/profile.d and open the shell script named 'colorls.sh' in a text editor. Look for the line "alias ls='ls --color=tty' 2>/dev/null" and copy and paste this line under itself. Now on the line pasted, replace 'ls' with 'c' and 'ls -color=tty' with 'clear -color=tty'. The edited colorls.sh file should look as in the screenshot above. Now open the terminal or konsole window, hit [Enter], and then [C] to check if the alias works. The alias should work even if you reboot your machine.

Team DigitTeam Digit

All of us are better than one of us.